Reverse HLOOKUP - Stumped...How do I do it??

[HeyMike]

Hi,

This is my first post after months of being an observer on this site. For some reason I can't solve this....
<BR>
<img src="http://www.freeimagehosting.net/newuploads/u6msu.jpg"></img>
<BR>

I am trying to type in an amount into cell C8 then have cell C10 display the company name from the table above.
<BR>
How do I do this? I tried an index and match and that didn't work because the match needs a specific row and the row is always changing depending on what is typed into C8. I tried inserting an OFFSET function into the "Lookup Array" of the MATCH, but that had issues when I expanded the above example to use it for my application.
<BR>
Any help would be greatly appreciated. Thanks!
<BR>
- Mike

 

[mgm05267]

Hi...

Try this...

=LOOKUP(C8,C2:E5,C2:E2)

Oops... never mind.... this is not you expected

 

[beedistinct]

=IF(OR(Company_A=$C$8),"Company A",IF(OR(Company_B=$C$8),"Company B",IF(OR(Company_C=$C$8),"Company C","No Match found!")))

First select the entore table including the Product column and the header row. Press CTRL+SHIFT+F3 and select the Top Row option.

Basically we just created Named ranges for the Company columns as Company_A, Company_B and Company_C.

This is an array formula so please remember to press CTRL + SHIFT + ENTER.

 

[jasonb75]

Try

=IF(COUNTIF($C$3:$E$5,C8),INDEX($C$2:$E$2,MIN(IF($C$3:$E$5=C8,COLUMN($C$3:$E$5)-COLUMN($C$3:$C$5)+1))),"No Match")

Array confirmed with Shift Ctrl Enter.

Multiple matches for C8 will return the leftmost result.

 

[jeffmb]

Also:

=IF(ISNUMBER(MATCH(C8,C3:C5,0)),C2,IF(ISNUMBER(MATCH(C8,D3:D5,0)),D2,IF(ISNUMBER(MATCH(C8,E3:E5,0)),E2,"No Match")))