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

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 he
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

Jacob:

Thanks very 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