A simple vlookup proving to be not so simple

Hi there,

Can someone please help me with a simple vlookup statement?

The attached spreadsheet contains 3 observations (rows), each of which has 3 outcomes (columns). The spreadsheet then determines, for each observation, the minimum value of the 3 outcomes ("Mini Max").

"Max" then determines what the largest value of these three minimums are.

I need "Winner" to get the index number for the trial associated with that maximum minimum. In this example, it would return the "2" from column B when the maximum minimum value is 502, or it would return 4 if the maximum minimum value was 199.

If anyone could help I would be eternally grateful

Try this in G9:

=SUMPRODUCT((C2:E7=G8)*(B2:B7))

Formula for G9

Formula:

Please Login or Register  to view this content.

My Solution only looks at the range ,G2:G7. The 0 specifies an exact match. In your sample the position in G2:G7 is your result.

To see how my solution works, use Evaluate Formula on the Formulas Ribbon to step through it. The first section generates a 3-column array of TRUE/FALSE values that resolve to 1 or 0. The 1 in this array is then multiplied by the number in the second array that is on the same row.

1*2 = 2

If 199 is the lookup value, it will return 4.

Yes in this scenario,

However you can add a constant or use the offset or the index functions if your requirement is more complicated.

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.