INDEX + MATCH(MAX) formula issue

Hi All

Ive been trying to solve a small problem I have with INDEX and MATCH formula, but couldnt find answer anywhere. Cutting long story short.

I have a MAX formula that looks ofr the highest value in several non-adjencent columns (ie. G, K and O). What I need now is a formula that would return me a value in columna A that is in the same row as Value found by MAX formula. I tried to use INDEX + MATCH formula, but I can only get it to work if I use MAX formula in single column. not for multiple.

So, it works fine, if I use following formula:

Cell 1 = MAX(G6:G28)
Cell 2 = =INDEX(A6:A28,MATCH(MAX(G6:G28),G6:G28,0))

Doesnt work with:

Cell 1 =MAX(G6:G28,K6:K28,O6:O28)
Cell 2 =INDEX(A6:A28,MATCH(MAX(G6:G28,K6:K28,O6:O28),(G6:G28,K6:K28,O6:O28),0))

Is it even possible to do?

I Attached example spreadsheet
yellow cells - range of cells MAX is looking for the highest value
blue cells - range of cells from which value should be returned using INDEX mATCH

Thanks in advance for any help

Not at all

This formula should do the trick:

Formula:

Please Login or Register  to view this content.

Let me know