VLOOKUP inconsistent ... help.

[Wayne]

I have a spreadsheet with 4 pages; each page has a simple list, with a number
in the left column and a letter in the right column.

On the various pages I do a vlookup on the first column of the others ...
some work and some just flat don't! How do I figure this out?

BTW ... spreadsheet is at http://www.heritage-dr.com/vlookup-prob.xls

How do I fix this?

Thanks in advance.

 

[Bryce]

At first glance, looks like you have copied/dragged your vlookup formulas,
without having absolute references in the lookup array. This makes the
lookup array change for each row you copy the formula into (relative
references). Put some $ signs around and you should be fine eg
vlookup(B2,'sheet1'!$A$2:$B$27,2,0). Even better may be to name your range
for each array

Sheet1 C2 =VLOOKUP(A2,Sheet2!A$2:B$27,2,FALSE) - ready to copy down
Sheet2 C2 =VLOOKUP(A2,Sheet3!A$2:B$27,2,FALSE) etc

 

[Mike H]

Hi,

It's not inconsistent it is doing exactly what your telling it to do. Take
this formula on sheet1

=VLOOKUP(A14,Sheet2!A15:B40,2,FALSE)

The value in A14 that your looking up is the number 13.
Now if we look at the llokup table on sheet 2 we find the value 13 is in
Cell A14 so it is outside the area bein referenced hence the #NA.

Now what I think you've done on sheet 1 is written the formula in C27 and
dragged up but because you haven't used absolute references you are getting
errors.

Put this in C2 of sheet 1 and drag down and the errors vanish and note the $
signs for absolute references. Repeat for other sheets

=VLOOKUP(A2,Sheet2!$A$2:$B$27,2,FALSE)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.