k1ng87
asked on
vlookup off by one row in range
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
I've attached an excel file showing the issue.
vlookup.xls
Neil Russell
This spreadsheet contains no vlookup formulas unless i am missing something?
ASKER
shoud be in cell E28
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.
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.
ASKER
so how should I adjust it then? and how come it works for numbers that fall "inside" the range?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this formula:
=IF(ISERROR(INDEX($A$28:$B
INDEX($A$28:$B$204,MATCH(D
INDEX($A$28:$B$204,MATCH(D
=IF(ISERROR(INDEX($A$28:$B
INDEX($A$28:$B$204,MATCH(D
INDEX($A$28:$B$204,MATCH(D
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.