Multiple lookup values in =HLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A classical formula might be =HLOOKUP(a1,a100:z100,1,FALSE). But I want to
display the result if the lookup value = a1 or a2 or a3 or...a10 ie: anything
in the range a1.a10, without writing a multiple nested statement. Any ideas?
Thanks.
 
Try...

=INDEX(A1:A10,MATCH(TRUE,INDEX(COUNTIF(A100:Z100,A1:A10)>0,0),0))

....confirmed with just ENTER, or...

=INDEX(A1:A10,MATCH(TRUE,COUNTIF(A100:Z100,A1:A10)>0,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Value 2
Lookup and count in the same formula 1
Find some text but not others 1
HLOOKUP Can't Search by Lookup Value 2
Lookup with multiple returns 12
lookup 3
Cell contents as the 'lookup value' parameter in HLOOKUP function 3
lookup to return true() values only and list 4

Back
Top