ExcelBanter - Multiple lookup values in =HLOOKUP

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Multiple lookup values in =HLOOKUP (https://www.excelbanter.com/excel-discussion-misc-queries/45892-multiple-lookup-values-%3Dhlookup.html)

Multiple lookup values in =HLOOKUP

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.
--
Peter
London, UK

Try...

=INDEX(A1:A10,MATCH(TRUE,INDEX(COUNTIF(A100:Z100,A 1: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!

In article ,
"Peter" wrote:

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.