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

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,E3 5,E43,E47),G35,IF(E43=MAX(E26,E35,E43,E47),G43,IF( E47=MAX(E26,E35,E43,E47),G47,""))))

Biff

"Len B" <gonehome@optusnet:con:au wrote in message
...
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

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

"T. Valko" wrote in message
...
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,E3 5,E43,E47),G35,IF(E43=MAX(E26,E35,E43,E47),G43,IF( E47=MAX(E26,E35,E43,E47),G47,""))))

Biff

"Len B" <gonehome@optusnet:con:au wrote in message
...
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

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" wrote:

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

"T. Valko" wrote in message
...
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,E3 5,E43,E47),G35,IF(E43=MAX(E26,E35,E43,E47),G43,IF( E47=MAX(E26,E35,E43,E47),G47,""))))

Biff

"Len B" <gonehome@optusnet:con:au wrote in message
...
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

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"
icrosoft.com wrote in
message ...
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" wrote:

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

"T. Valko" wrote in message
...
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,E3 5,E43,E47),G35,IF(E43=MAX(E26,E35,E43,E47),G43,IF( E47=MAX(E26,E35,E43,E47),G47,""))))

Biff

"Len B" <gonehome@optusnet:con:au wrote in message
...
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