Formula depends on which cell contains max.

[Len B]

I have a worksheet where I need to select a value based on which cell in a
non-contiguous range has the MAXimum value.

To be more specific,
There are 4 cells in column E (26, 35, 43, 47) each with values < 200.
There are 4 matching cells in column G.
If say E35 is the largest of the 4 values then the value in G35 is to be
used as part of a formula in G59.

I guess I could always create a contiguous table and use VLOOKUP but the
layout is repeated 200 more times down the sheet and I would rather save the
space. Besides, I just know there has to be a formula. I have played with
MAX and MATCH and CELL("row"

Is there another function I should look into. Any suggestions?

TIA
-- Len

 

[T. Valko]

Are there any numeric values in the cells between the specific range cells?

Will there be only one instance of the max value?

No real elegant way to do this. Brute force:

=IF(E26=MAX(E26,E35,E43,E47),G26,IF(E35=MAX(E26,E35,E43,E47),G35,IF(E43=MAX(E26,E35,E43,E47),G43,IF(E47=MAX(E26,E35,E43,E47),G47,""))))

Biff

 

[Len B]

Thanks Biff.
Yes there will be values in between the specific cells. Each cell heads its
own column of values reaching the next header cell. E47 heads a column of 4
values so the range is E26:E50 and the possibility exists that a value may
be larger than the desired max from the 4 specified cells so that rules out
taking the max from E26:E50 unfortunately.

Yes, It is possible for 2 of the 4 to have the same max value but choosing
the first will be ok. It will not matter because the whole scheme is
designed to predict what someone else will choose.

Thanks again
-- Len

 

[Yogi Anand--www.energyefficientbuild.com]

Hi Len:

Based on the same idea as T. Valko's ... but to shorten the formula,
I defined LenMax as

=Max($E$26,$E$35,$E$43,$E$47)

and then used

=IF(LenMax=E26,G26,IF(LenMax=E35,G35,IF(LenMax=E43,G43,IF(LenMax-E47,G47))))

 

[Len B]

Thanks Yogi
That would work well but this framework is repeated 200 times down the sheet
so I wouldn't use a named range, just choose a vacant cell (G55) and set =
Max(E26,E35,E43,E47) then =IF(G55=E26,G26,IF(G55=E35... using relative
rather than absolute addressing. this will allow me to copy the framework
down.

Thanks to both of you.
-- Len

"Yogi Anand--www.energyefficientbuild.com"