Vlookup #n/a

[phayes7]

I have tried everything & I keep getting #n/a returned in my vlookup formula.
I have tried IF and ISNA and they do not work. I know the formula is correct.
Plese help!

=VLOOKUP(B16,Sheet1!B:F,5,FALSE
=IF(ISNA(VLOOKUP(B16,Sheet1!B:F,5,FALSE))=TRUE,"Test",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

 

[Bob Bridges]

#N/A doesn't mean the formula is wrong, it just means it looked for your
value and couldn't find it in the table you defined. Let's take a look
here...yeah, looks fine to me, and if the second formula returns "Test" then
that proves it. It's not the formula itself, it's just that VLOOKUP failed
to find the value in B16.

 

[phayes7]

Thanks for getting back so fast. I validated that the values in column 16 are
in the array I'm searching and that is what is perplexing. I chave changed
the cell format to number, general and text and still nothing.

Thanks again!

 

[Bob Bridges]

Well, I've seen some things from time to time that look like matches but
aren't, quite. For example, a trailing space in either value (the one in B16
or the one in Sheet1!F) will do it. I think the search is case-insensitive,
unless you have a setting changed, but it's something to experiment. If you
can't find the problem, you can send me a copy of the workbook at
(e-mail address removed) and I'll see whether I can't figure it out.

 

[Yong Heng]

I've encountered this problem before, it can be frustrating.

I find one useful diagnosis is to try doing a B16=(column containing the
seemingly same value) and find out more about what went wrong.

Yong Heng

 

[Dave Peterson]

Debra Dalgleish has lots of notes on troubleshooting this kind of error:
http://contextures.com/xlFunctions02.html#Trouble

 

[phayes7]

Thanks to Dave & Bob...I went to the link from Debra and tried converting the
text to numbers advice. I had done this before but in the manner described.
It worked...again thanks to all!