VLOOKUP question

[goodmachine]

I am attempting to match values from one worksheet to another. The values in Column A can be just numbers or alphanumeric. Some of the values can have a leading 0 as well. In the table array, the values can also be just numbers, alphanumeric and/or have a leading 0.

The lookup value is 2708000802. This value is in the table array and I'm expecting the VLOOKUP to return 2708000802 in column B where the lookup value is stored (where I inserted the formula). However, the VLOOKUP returns the N/A error.

• I have formatted both worksheets so that the category is GENERAL
• I have converted the 2708000802 in the table array to a number
• I have multiplied the 270800802 in the table array by 1

None of these changes have given me the result I'm looking for.

One thing to note:

• In the lookup value and the table array columns, the green diamond error appears in a lot of the cells indicating that the "cell is formatted as text or preceded by an apostrophe"

I thought by formatting the both worksheets as general, this would help.

Any help on this matter is greatly appreciated. I'm still learning about this function and how to troubleshoot the errors.

 

[FDibbins]

1st, formatting does not change the contents of the cell, only how it appears - it a cosmetic change

2nd, those cells that have green diamond - highlight them all, click the diamond and click "convert to number" Then see if that helps?

If not, can you provide a sample of what you are working with?

 

[Superstar31]

I am attempting to match values from one worksheet to another. The values in Column A can be just numbers or alphanumeric. Some of the values can have a leading 0 as well. In the table array, the values can also be just numbers, alphanumeric and/or have a leading 0.

The lookup value is 2708000802. This value is in the table array and I'm expecting the VLOOKUP to return 2708000802 in column B where the lookup value is stored (where I inserted the formula). However, the VLOOKUP returns the N/A error.

• I have formatted both worksheets so that the category is GENERAL
• I have converted the 2708000802 in the table array to a number
• I have multiplied the 270800802 in the table array by 1

None of these changes have given me the result I'm looking for.

One thing to note:

• In the lookup value and the table array columns, the green diamond error appears in a lot of the cells indicating that the "cell is formatted as text or preceded by an apostrophe"

I thought by formatting the both worksheets as general, this would help.

Any help on this matter is greatly appreciated. I'm still learning about this function and how to troubleshoot the errors.

can you upload the file somewhere or give us a sample to play with? and can we see the forumla you're using?

 

[goodmachine]

I would like to attach a sample file, but I don't see the option to do so. I see the option to insert video and image, but no file attachment. I went to "Go Advanced," but I don't see the attach file option there either.

 

[Aladin Akyurek]

Care to post the formula you tried?

 

[goodmachine]

Below is a sample of the look up value. I'm trying to look up the Item Number on a separate worksheet with the Item Numbers that correspond to a Y or N for the Medicare value for that corresponding Item number. The formula that I am applying to Column B (Medicare) is =VLOOKUP(A2,Sheet21!A1:B1289,1,FALSE). The first Item Number is on the table array, but it's still showing up as #N/A. I can't seem to figure out what's going on. It's very frustrating to say the least. Any help is appreciated. Thank you.

Item Number	Medicare
88307	#N/A
7760002320
8004000134
8004000234
8004000334
8004000434
8004000534
8004000634
8660004002
8660007536

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Below is a sample of the look up value. I'm trying to look up the Item Number on a separate worksheet with the Item Numbers that correspond to a Y or N for the Medicare value for that corresponding Item number. The formula that I am applying to Column B (Medicare) is =VLOOKUP(A2,Sheet21!A1:B1289,1,FALSE). The first Item Number is on the table array, but it's still showing up as #N/A. I can't seem to figure out what's going on. It's very frustrating to say the least. Any help is appreciated. Thank you.

Item Number	Medicare
88307	#N/A
7760002320
8004000134
8004000234
8004000334
8004000434
8004000534
8004000634
8660004002
8660007536

<tbody>
</tbody>

Care to explain what the above exhibit means?

 

[markmzz]

Try this formula:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=VLOOKUP(A2&"",Sheet21!$A$1:$B$1289&"",1,0)

And tell me the result.

Markmzz

 

[goodmachine]

Try this formula:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=VLOOKUP(A2&"",Sheet21!$A$1:$B$1289&"",1,0)

And tell me the result.

Markmzz

I get the #VALUE error when I enter that formulat in cell B2.

 

[Aladin Akyurek]

Care to answer the question in post #7?

What do you have exactly in A2 and which value do you expect this in Sheet2!A1:A1289 to match?