Solved

LOOKUP value when range of values is not sorted or in order in Excel

Posted on 2011-02-28
4
195 Views
Last Modified: 2012-05-11
Hello,

What is the best way to look up a value in an Excel (2007) spreadsheet when the range containing the value cannot be sorted and/or contains a variety of different types of entries (text, numbers, etc.)?

I have tried several different things but I keep getting #REF! as the result.

Thanks
0
Comment
Question by:Steve_Brady
  • 2
4 Comments
 
LVL 6

Expert Comment

by:FernandoFernandes
Comment Utility
use vlookup() or match() . . .
vlookup() with the last parameter as a zero (or false)...
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
Hello Steve,

What do you want to do when the value is found? A standard VLOOKUP with FALSE as the last argument doesn't require anything to be sorted. For example to lookup A2 in C2:C10 and return the corresponding value from D2:D10

=VLOOKUP(A2,C2:D10,2,FALSE)

A2 must match in terms of format, e.g. if A2 is a text formatted number then the match in column C must also be text-formatted etc.

regards, barry
0
 
LVL 6

Expert Comment

by:FernandoFernandes
Comment Utility
example:

=VLOOKUP(A1,B1:Z1000,15,0)
or
=VLOOKUP(A1,B1:Z1000,15,FALSE)
or if you only want to see the row where it is...
=MATCH(A1,B1:B1000,0)
0
 

Author Comment

by:Steve_Brady
Comment Utility
Thanks for the responses.

barryhoudini:
>>A2 must match in terms of format, e.g. if A2 is a text formatted number then the match in column C must also be text-formatted etc.


Yes, I tried =VLOOKUP but had the same problem.  However, I wasn't aware of the requirement to match formatting.  That was the problem.

Thanks Barry!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now