Help on Lookup feature

J

JLH

I am having difficulty getting a lookup function to work properly. In a
worksheet using 165 rows looking up data from another worksheet using
165 rows, looking at a unique number, some rows return correct data,
and others return the #N/A error message. Any suggestions?

Thanks much.

Joe
 
J

Jacob_F_Roecker

I've gotten this before

Go to the first row where you get you "N/A" and look at the cell range of
the Vlookup. Compare that to the first cell with the formula and you may
notice a difference in the cell ranges for the lookup.

How this happened is you used the copy-->paste method of putting your
formula in each cell without making the range reference absolute.

To fix the problem put a $ before the row and column reference of your range
your looking up.

=VLOOKUP($A$2,Sheet3!$G18:$V$28,8,FALSE)

I would also recommend Allen Wyatt's explanation of the feature here:
http://exceltips.vitalnews.com/Pages/T0627_Understanding_the_VLOOKUP_Function.html

The only other way I've gotten N/A is if the data just simply isn't there.
I don't want to be printing N/A's on my final report and so I usually use the
=IF(ISERROR() formula method.

=IF(ISERROR(VLOOKUP($A$2,Sheet3!$G18:$V$28,8,FALSE)),"
",VLOOKUP($A$2,Sheet3!$G18:$V$28,8,FALSE))


I hope this helps!
 
Top