[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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
0
Steve_Brady
Asked:
Steve_Brady
  • 2
1 Solution
 
FernandoFernandesCommented:
use vlookup() or match() . . .
vlookup() with the last parameter as a zero (or false)...
0
 
barry houdiniCommented:
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
 
FernandoFernandesCommented:
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
 
Steve_BradyAuthor Commented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now