How to find the value with 2 conditions?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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",FA2:FA14,0))=FA2:FA14,0))

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

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