View original (French)

Social security number search #N/A

MARYLOU95 Posted messages 6 Status Member -  
jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   -
Hello,

I want to perform a VLOOKUP on a Social Security number, and I get the response #N/A
I modified the format to number and still the same error

example
1st file
CJT NON A CHARGE / SUSPENSION 285XXXXXXXXXX #N/A
2nd file
F.M. ENSEMBLE DU PERSONNEL 285XXXXXXXXXX

thank you for your help

Marylou

(Personal information removed by Moderation CCM)

3 answers

  1.
    Mazzaropi Posted messages 1983 Registration date   Status Contributor Last intervention   72
     
    Marylou, Good evening.

    This explanation alone will not help you.

    Please let us know how the layout of your spreadsheet is.
    --
    Belo Horizonte, Brasil.
    Marcílio Lobão
    0
  2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello Marylou.

    Your data is in text; therefore, we cannot format it into numbers! If you really want help, please send your Excel file: 
     1) Go to http://cjoint.com/
     2) Click on [Browse] to select your file (max 8192 Ko)
     3) Scroll down to click the blue button [Create Cjoint link] 
     4) After a few seconds, the second page will appear, with the link in bold; select it and click "Copy"
     5) Return to your discussion on CCM, and in your reply message, click "Paste".
    =>See the guide https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers

    --
    Retirement is nice! Especially in the Antilles... :-)
    Raymond (INSA, AFPA, CF/R)
    0
  3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Have you seen what your social security number looks like in the SAP IT 64 sheet?
    Make sure to set the column to Text format before pasting your values.

    And when you upload a file, replace personal data with dummy information. This could get you in trouble with Pôle Emploi...
    I deleted your file.
    Eric

    By trying continuously, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to saying thank you (yes, it does happen!!!), remember to mark it as resolved. Thank you.
    0
    1. MARYLOU95 Posted messages 6 Status Member
       
      Hello,
      ok thank you
      indeed you are right about my file
      I will be careful next time
      marylou
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      No, but it was the answer to your problem, not a judgment.
      Change the column to Text format before pasting your values
      And your social security numbers will remain intact for the search.
      0
    3. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
       
      Hello,
      In addition to the cell format .. it seems to me that the VLOOKUP function can only search in the FIRST column of a matrix....
      To look for data that is in the third column (of your file 2), it's better to use INDEX/MATCH
      For example: 

      =INDEX('2nd file'!A1:G28,MATCH('1st file'!B2,'2nd file'!C1:C28,0),6)
      0