INDEX MATCH as arrayfunction using MAX and PRODUCT

Originally Posted by The_Snook

Hi Sherk,

If I'm understanding your query correctly try this in cell B2. This will return the values from the corresponding table.

=VLOOKUP(A2,$G$2:$J$5,MATCH(1,C2:E2,0)+1)

To return the text reference use:

=INDEX($C$9:$E$9,MATCH(1,C10:E10,0))

Regards,

Snook

I think you've misunderstood my question as you're not doing a looking up the minimum or doing any multiplication of the tables...

Br,
Sherk

Originally Posted by xlnitwit

Hi,

An array formula
=INDEX($H$1:$J$1;MATCH(MIN(H2:J2*C2:E2);H2:J2*C2:E2;0))

I feel that posting a workbook would have been more helpful than a picture.

Thanks for the reply and although it's closer to the mark than the previous answer it's not quite enough as the tables don't correspond row by row, some sort of IF needs to be used I think.

Sure, here's a workbook where I think the need for an IF-statement is more obvious!

Br,
Sherk

Originally Posted by The_Snook

Try this as an array formula (it's xlnitwit's solution with a slight tweak):

=INDEX($H$1:$J$1,MATCH(MIN(H2:J2*C2:E2),H2:J2*C2:E2,0))

Thanks for keep going at it, it's the same as xlnitwit responded before so have a look at my response to him/her where I've also included a workbook!

Br,
Sherk

Give this one a whirl:

=INDEX($H$1:$J$1,MATCH(MIN(INDIRECT("H"&MATCH(A2,G$1:G$5,0)):INDIRECT("J"&MATCH(A2,G$1:G$5,0))*C2:E2),INDIRECT("H"&MATCH(A2,G$1:G$5,0)):INDIRECT("J"&MATCH(A2,G$1:G$5,0))*C2:E2,0))

Or ...

=INDEX($H$1:$J$1,MATCH(MIN(C2:E2*INDEX($H$2:$J$5,MATCH(A2,$G$2:$G$5,0),0)),
C2:E2*INDEX($H$2:$J$5,MATCH(A2,$G$2:$G$5,0),0),0))

Originally Posted by The_Snook

Give this one a whirl:

=INDEX($H$1:$J$1,MATCH(MIN(INDIRECT("H"&MATCH(A2,G$1:G$5,0)):INDIRECT("J"&MATCH(A2,G$1:G$5,0))*C2:E2),INDIRECT("H"&MATCH(A2,G$1:G$5,0)):INDIRECT("J"&MATCH(A2,G$1:G$5,0))*C2:E2,0))

Originally Posted by xlnitwit

I believe this array formula will meet your requirements, provided that there will never be more than one match for the minimum

=INDEX($H$1:$J$1,MATCH(MIN(INDEX($H$2:$J$5,MATCH(A2,$G$2:$G$5,0),)*C2:E2),INDEX($H$2:$J$5,MATCH(A2,$G$2:$G$5,0),)*C2:E2,0))

Both seem to be working fine, xlnitwit's solution is a bit simpler but who cares! Thanks a lot to the both of you!