Does anyone have any suggestions on how to find the value with 2 conditions?
There is a list of date under EY column, and a list of number under FA column.
I would like to find the date, whch month is Feb under column EY with the
max numbers within Feb. For example,

01.27 0
01.28 0
01.29 0
01.30 2
01.31 3
02.01 5, it should return 02.01 on FC column based on this row
02.02 3
02.03 2
02.04 0
02.05 0
02.06 1
02.07 2

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Hi,

Assuming your dates are entered as three digit months Jan, Feb,... the
following array formula will do the trick:

=INDEX(FC2:FC14,MATCH(TRUE,MAX(IF(EY2:EY14="Feb",F A2:FA14,0))=FA2:FA14,0))

To make this an array press Shift+Ctrl+Enter to enter the formula not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Eric" wrote:

Does anyone have any suggestions on how to find the value with 2 conditions?
There is a list of date under EY column, and a list of number under FA column.
I would like to find the date, whch month is Feb under column EY with the
max numbers within Feb. For example,

01.27 0
01.28 0
01.29 0
01.30 2
01.31 3
02.01 5, it should return 02.01 on FC column based on this row
02.02 3
02.03 2
02.04 0
02.05 0
02.06 1
02.07 2

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

another way:

=MAX(IF((ISNUMBER($FA$2:$FA$28))*(MONTH($EY$2:$EY$ 28)=2);$FA$2:$FA
$28;""))


On 6 Mar, 08:53, Shane Devenshire
wrote:
Hi,

Assuming your dates are entered as three digit months Jan, Feb,... the
following array formula will do the trick:

=INDEX(FC2:FC14,MATCH(TRUE,MAX(IF(EY2:EY14="Feb",F A2:FA14,0))=FA2:FA14,0))

To make this an array press Shift+Ctrl+Enter to enter the formula not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire



"Eric" wrote:
Does anyone have any suggestions on how to find the value with 2 conditions?
There is a list of date under EY column, and a list of number under FA column.
I would like to find the date, whch month is Feb under column EY with the
max numbers within Feb. For example,

01.27 * * *0
01.28 * * *0
01.29 * * *0
01.30 * * *2
01.31 * * *3
02.01 * * *5, it should return 02.01 on FC column based on this row
02.02 * * *3
02.03 * * *2
02.04 * * *0
02.05 * * *0
02.06 * * *1
02.07 * * *2

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

replace ; with ,
sorry

On 6 Mar, 09:08, Jarek Kujawa wrote:
another way:

=MAX(IF((ISNUMBER($FA$2:$FA$28))*(MONTH($EY$2:$EY$ 28)=2);$FA$2:$FA
$28;""))

On 6 Mar, 08:53, Shane Devenshire
wrote:



Hi,

Assuming your dates are entered as three digit months Jan, Feb,... the
following array formula will do the trick:

=INDEX(FC2:FC14,MATCH(TRUE,MAX(IF(EY2:EY14="Feb",F A2:FA14,0))=FA2:FA14,0))

To make this an array press Shift+Ctrl+Enter to enter the formula not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Eric" wrote:
Does anyone have any suggestions on how to find the value with 2 conditions?
There is a list of date under EY column, and a list of number under FA column.
I would like to find the date, whch month is Feb under column EY with the
max numbers within Feb. For example,

01.27 Â* Â* Â*0
01.28 Â* Â* Â*0
01.29 Â* Â* Â*0
01.30 Â* Â* Â*2
01.31 Â* Â* Â*3
02.01 Â* Â* Â*5, it should return 02.01 on FC column based on this row
02.02 Â* Â* Â*3
02.03 Â* Â* Â*2
02.04 Â* Â* Â*0
02.05 Â* Â* Â*0
02.06 Â* Â* Â*1
02.07 Â* Â* Â*2

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -