Vlookup returning N/A when the value is there

[McNeil87]

Hello!

my simple vlookup is returning an N/A result when the value looked up is there.

it’s just a simple lookup.. =vlookup(A2,tablearray, lookup column index,column cell, false). I have the lookup Column to the left of the left most column.
Any suggestions why this isn’t working? I don’t need to add in an N/A statement because my return statement will be the same.

thank you!

 

[Toadstool]

Hi McNeil87,

Most of the time it's because your lookup value and array you're searching are of different data types.

 

[Yongle]

it's returning n/a because the value that Excel is looking for is not there to be found
it may look the same to you but not to Excel
what type of value is in A2 ?

 

[joeu2004]

My first suggestion is: show us the actual formula, not some abstraction. Copy-and-paste from the Formula Bar, adding curly braces, if any, which are stripped away when we copy from the FB.

The syntax that you describe is incorrect. There is no "lookup column index" parameter; and it is unclear what you mean by "column cell". If is also unclear what you mean by "I have the lookup column to the left of the left-most column".

The correct syntax is demonstrated by VLOOKUP(A2, B1:J100, 2, FALSE), which means: lookup A2 in the left-most column of the second parameter (i.e. B1:B100), and return the corresponding value in the 2nd column of the second parameter (i.e. C1:C100).

As to the #N/A error.... There might be several explanations. The first is that you used the wrong parameters, in the first place.

But assuming your VLOOKUP has the proper syntax, verify that the value in A2 and the matching value in B1:B100 (in my example) are the same type (text or numeric).

The format of the cells does not matter. And visual inspection can be misleading. Use formulas of the form =ISNUMBER(A2) and =ISNUMBER(B50) to confirm that they are the same type.

 

[McNeil87]

Hello all! Thank you for these tips.. very helpful. I discovered my lookup numbers were read as ‘text.’ Once I converted it to number it worked! Something for the future when I paste values in.

thank you!!