Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

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

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
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image
use vlookup() or match() . . .
vlookup() with the last parameter as a zero (or false)...
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image
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)
Avatar of Steve_Brady
Steve_Brady
Flag of United States of America image

ASKER

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!