Getting HLOOKUP to give me what I really want!?

[Bill Bisco]

Hi there.

I'd like to get a certain result from HLOOKUP, but it seems unable to. For example take a look at the following Scenario and Code:



The Code doesn't work because the Headings (option1, Option2, etc.) are in the first row and HLOOKUP requires that only values be in the first row.

Is there another code or way to make it so that I can find a certain value, and then have the cell display the Option that Value belongs to?

Any help is appreciated,
Bill

 

[Brianwarnock]

Its a few years since i did this but i think that you need to use a combination of Index and Match.

Brian

 

[Brianwarnock]

Yep that works but remember that you are using an Array formula, my simplest formula was

=INDEX(A1:C3,,MATCH(MIN(A2:C2),A2:C2,0))


Brian

 

[Bill Bisco]

Brian,

Truly Truly Thank you. This has been bothering me all morning. I knew the solution was not that complicated!

Sincerely,
Bill