Returning headers using index match

**dmr316** · 12-03-2009, 12:13 PM

Hello,

I have an Excel problem I can't get my head around, so I hope someone here will be able to help.

I have a much larger table in this form:

London Paris NY Tokyo
Qantas £455.52 £471.17 £441.53 £396.03
BritAir £453.92 £468.30 £456.27 £453.60
American £472.61 £459.38 £459.38 £369.72
AirFra £473.70 £426.62 £468.30 £486.59

In row 6, I've got "lowest value", e.g. B6=MIN(B2:B5)
In row 7, I'm using index-match to tell me which airline provides that price, e.g. B7=INDEX($A2:$A5,MATCH(B6,B2:B5,FALSE),1)

However, while I'm comfortable with using index-match where vlookup doesn't work, I'm really struggling with using index-match where hlookup doesn't work. Assuming that index-match is able to replace hlookup?

For example, in column 6 while I am able to obtain the lowest price, e.g. F2=MIN(B2:E2) I am completely unable to return the destination associated with that lowest price, i.e. I don't know what formula would go in cell G2.

I have looked on the internet for something similar but can't find anything I can modify. So I felt it would be best to ask the experts who'll be able to hold my hand through this.

Thanking you in advance.

**DonkeyOte** · 12-03-2009, 12:17 PM

Similar logic

G2: =INDEX($B$1:$E$1,MATCH($F2,$B2:$E2,0))
copied down

I should clarify... given the range is a vector (single row) there is no need to specify both row & column you need only specify the value required - in this case column index.

The same logic could be applied to your row 7 formula such that

B7: =INDEX($A$2:$A$5,MATCH(B$6,B$2:B$5,0))
copied across

In the above the range is a vector (single column) and so we need specify only the relevant value - in this case the row index.