Help on Lookup feature

[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

[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..._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!


"JLH" wrote:

>
> 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
>
>
> --
> JLH
> ------------------------------------------------------------------------
> JLH's Profile: http://www.excelforum.com/member.php...o&userid=29940
> View this thread: http://www.excelforum.com/showthread...hreadid=496410
>
>

[JLH]

Jacob:

Thanks very much!

Joe