VLOOKUP in two worksheets

[Silena K-K]

Hi there

I have "product information" in one worksheet and in the second sheet
"tracking information" of where the product is in the lab.

Based on a unique id in the product information worksheet I want to look up
product details such as Name, Date, Storage Time, etc and have it
automatically inserted in the Tracking worksheet.

An example of my VLOOKUP formula is: =VLOOKUP(A3,'Stability Sample
Register'!A5:E23,4,FALSE) but it returns #N/A.

What am I doing wrong?

Thanks for you help, Silena

 

[RobN]

Hi Silena,

When I copied your formula into a spreadsheet and set up a workbook with a
worksheet by that name, it also returned the #NA error. However, when I
highlighted 'Stability Sample Register'!A5:E23 in the formula bar & selected
that worksheet and the range and pressed enter, the error went away. Odd I
know! I don't know how you constructed it, but maybe give that a try. ie,
try to construct the whole formula by typing only =VLOOKUP( select cell A3
with the mouse, type a comma and then select the worksheet and range with
your mouse, then type in ,4,False).

Rob

 

[Dave Peterson]

The #n/a means that there is no match.

If you think that there is a match and excel is screwing up, check out Debra
Dalgleish's site to help trouble shoot your formula (and data!).

http://contextures.com/xlFunctions02.html#Trouble

If you know that there isn't a match and want to hide the #n/a, you can use a
formula like:

=if(isna(yourvlookupformula),"",yourvlookupformula)

In xl2007, you can use =iferror(), too.