Vlookup #n/a
 

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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

Vlookup #n/a
 

#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" wrote:
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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

Vlookup #n/a
 

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" wrote:

#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" wrote:
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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

Vlookup #n/a
 

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
and I'll see whether I can't figure it out.

--- "phayes7" wrote:
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.

--- "Bob Bridges" wrote:
#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" wrote:
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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

Vlookup #n/a
 

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

"phayes7" wrote:

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" wrote:

#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" wrote:
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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

Vlookup #n/a
 

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

phayes7 wrote:

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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

--

Dave Peterson

Vlookup #n/a
 

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!

"Dave Peterson" wrote:

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

phayes7 wrote:

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,"Te st",VLOOKUP(B16,Sheet1!B:F,5,FALSE)) Note: I just get test returned
=IF(B16="","",VLOOKUP(B16,Sheet1!B:F,5,FALSE))

--

Dave Peterson