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

Posted on 2011-02-28
Last Modified: 2012-05-11

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.

Question by:Steve_Brady
  • 2

Expert Comment

ID: 35003000
use vlookup() or match() . . .
vlookup() with the last parameter as a zero (or false)...
LVL 50

Accepted Solution

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


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

Expert Comment

ID: 35003008

or if you only want to see the row where it is...

Author Comment

ID: 35158092
Thanks for the responses.

>>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!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

16 Experts available now in Live!

Get 1:1 Help Now