Solved

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

Posted on 2011-02-28
195 Views
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
• 2

LVL 6

Expert Comment

use vlookup() or match() . . .
vlookup() with the last parameter as a zero (or false)...
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
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

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

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

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 …