[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
7
Medium Priority
?
572 Views
Last Modified: 2012-05-11
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
Comment
Question by:k1ng87
  • 4
  • 2
7 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35732593
This spreadsheet contains no vlookup formulas unless i am missing something?
0
 
LVL 1

Author Comment

by:k1ng87
ID: 35732605
shoud be in cell E28
0
 
LVL 37

Expert Comment

by:Neil Russell
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:k1ng87
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

by:
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.

Read the online help for VLOOKUP and examine the range description fully.
0
 
LVL 8

Expert Comment

by:wchh
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

by:Neil Russell
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question