[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# vlookup off by one row in range

Posted on 2011-05-10
Medium Priority
572 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
• 4
• 2

LVL 37

Expert Comment

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

LVL 1

Author Comment

ID: 35732605
shoud be in cell E28
0

LVL 37

Expert Comment

ID: 35732675
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

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

LVL 37

Accepted Solution

Neil Russell earned 2000 total points
ID: 35732780
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

ID: 35735294
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

ID: 35735633
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URLās and adds a header titled āURL Listā. It then searches through all URLās in column āAā, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLās are then highligā¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month18 days, 12 hours left to enroll