Solved

# vlookup off by one row in range

Posted on 2011-05-10
508 Views
So I have a vlookup that hits a range but the value one row below in some cases. I've noticed that it happens when the look up value is at the beginning of the range it falls under.

I've attached an excel file showing the issue.
vlookup.xls
0
Question by:k1ng87

LVL 37

Expert Comment

This spreadsheet contains no vlookup formulas unless i am missing something?
0

LVL 1

Author Comment

shoud be in cell E28
0

LVL 37

Expert Comment

You idea of a RANGE Lookup and Microsofts are very different!

Your cells in A28 reading down really need to have the whole table inserted.  EACH item on a row of its own.

010-024

IS NOT A RANGE

It is a string of chars that is treated as a whole string.
0

LVL 1

Author Comment

so how should I adjust it then? and how come it works for numbers that fall "inside" the range?
0

LVL 37

Accepted Solution

You should list each start and end destination in column A with the Zone in B.
000  NA
004  NA
005  3
010  2
024  2
025  3
029  3

etc...

Then your formula will work as it return the value that MATCHES or is the NEXT HIGHEST MATCH.

0

LVL 8

Expert Comment

Try this formula:
=IF(ISERROR(INDEX(\$A\$28:\$B\$204,MATCH(D28 & "*",\$A\$28:\$A\$204,0),2)),
INDEX(\$A\$28:\$B\$204,MATCH(D28 & "*",\$A\$28:\$A\$204,1),2),
INDEX(\$A\$28:\$B\$204,MATCH(D28 & "*",\$A\$28:\$A\$204,0),2))
0

LVL 37

Expert Comment

Also note that the data in YOUR column A has a mixture of both numeric and text entries in the cells. VLOOKUP will not function correctly with this mixture.
0

## Featured Post

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.