VLOOKUP: Wont return value

M

mind_killer

Hi,
I have the opposite problem of some of those here. VLOOKUP is returning #N/A
when it should be returning a value.

The formula I am using is:
=VLOOKUP($B38,Roster!$B:$F,2,TRUE)

The lookup value is a date, I have been playing around with the format but
it has not fixed it.

Thanks
 
J

Jacob Skaria

Try with a defined range....

=VLOOKUP($B38,Roster!$B1:$F1000,2,FALSE)

If this post helps click Yes
 
H

Harlan Grove

mind_killer said:
Hi,
I have the opposite problem of some of those here. VLOOKUP is returning #N/A
when it should be returning a value.

The formula I am using is:
=VLOOKUP($B38,Roster!$B:$F,2,TRUE)

The lookup value is a date, I have been playing around with the format but
it has not fixed it.

If B38 in the same worksheet as this formula were a number but the
values in Roster!B:B were text, you'll get #N/A whether the 4th
argument to VLOOKUP is TRUE or FALSE.

What do the formulas =COUNT(B38) and =COUNT(Roster!B:B) return?

Formatting has NO EFFECT on cell values. If Roster!B:B does contain
text, the select it and run the menu command Data > Text to Columns,
and when the dialog appears just click on the Finish button. That
should convert all valid dates to numeric date values.
 
A

Archimedes' Lever

I am guessing here...

The lookup value must be formatted differently. Not the data you want to
fill. The data you use to refer to it with... The "key" data.

$B38 is formatted differently than the cell in the lookup range. OR B38
is empty, and you are offset from what you think your lookup key cell
location is. OR the key lookup value is not in the lookup data range
named. It has to be the first column of the range as well.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top