How do I avoid #NA result in a VLOOKUP

[VKL Narayanan]

I am trying to vlookup a number stored as text. I have verified and
reverified the formula nothing seems to be wrong, the syntex is correct and
the field references are correct, but it keeps returning for all cells #NA.
How to debug this?

 

[Bernard Liengme]

It would help if you showed us the formula and some sample table data
best wishes

 

[VKL Narayanan]

Thanks

The formula is reproduced below

=VLOOKUP(D80,$D$923:$F$1025,3,FALSE)

D80 is a numbe formatted as text, the arry is given below

Account Description Set
2011001 akfjdkljfl Loc+CC
2021001 akfjdkljfl Loc+CC

D80 is searching for values under the column "Account" and I have a value
2011001 in D80 and it is still returning #NA, I formatted the column as text,
deleted and pasted the values, but nothing is working.

 

[Bob Phillips]

Try

=VLOOKUP(--D80,$D$923:$F$1025,3,FALSE)

 

[VKL Narayanan]

ok, thanks, I tried and it worked,

what is the meaning of adding --

 

[Jacob Skaria]

Hi Narayanan

-- converts boolean values to 1 and 0. Try the below formulas

=--TRUE returns 1
=--FALSE returns 0

Take a look at JE McGimpsey's site:
http://www.mcgimpsey.com/excel/timestamp.html


If this post helps click Yes

 

[Jacob Skaria]

Oops; I have pasted the wrong link; It should have been

http://mcgimpsey.com/excel/formulae/doubleneg.html

If this post helps click Yes

 

[Shane Devenshire]

Hi,

The lookup value AND the lookup column in the lookup table must all be of
the same data type. Either both text or both numbers. You can use the VALUE
function to convert text numbers to number but the -- trick is favored by
most of the experts here.

Although I wouldn't do it you could convert the table range, within the
formula to text instead of converting the lookup number to a value.

 

[Bob Phillips]

It actually converts the text value to its numeric equivalent here.