Excel Formula

[hrayani]

Hi All

I need a formula. Here is a sample data

DATE	P.O. #	Value
1-Oct-18	308211	-
2-Oct-18	308211	-
3-Oct-18	308211	-
4-Oct-18	308211	21,085
8-Oct-18	308211	-
9-Oct-18	308211	282
11-Oct-18	308211	16,607
20-Oct-18	301675	-
23-Oct-18	301675	-
24-Oct-18	301675	-
25-Oct-18	301675	-
26-Oct-18	301675	-
27-Oct-18	301675	1,522
29-Oct-18	301675	-
30-Oct-18	301675	-
1-Nov-18	301675	-
5-Nov-18	301675	9,211
7-Nov-18	301675	14,080

<tbody>
</tbody>

Give oldest date from column A against po number 308211 where the value >0 in Last Column

If Po # is 308211.... Answer should be 4-Oct-18. Coz there are no values in column C
for dates 1st-Oct-18 to 3rd Oct-18

Similarly
If Po # is 301675.... Answer should be 27-Oct-18

Hope anyone can provide a solution

Regards,

Humayun

 

[James006]

Hello,

With your data in range A1:C9

you can test following Array Formula:

=INDEX(A1:A19,MIN(IF(B1:B19=B2,IF(C1:C19<>"-",ROW(C1:C19)))))

Hope this will help

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=INDEX($A$2:$A$19,MATCH(1,IF($B$2:$B$19=F2,IF(ISNUMBER($C$2:$C$19),1)),0))

where F2 = 308211 and F3 = 308211.

 

[hrayani]

Thanks James & Aladin for the reply,


Here is another way i found myself.

=MIN(IF(B2:B29=F1,IF(C2:C29>0,A2:A29)))

Works Perfect......

But i would like to ask one thing - that is if i combine 2 condition with an AND in the formula then the formula is not giving the correct result.
Here is the formula

=MIN(IF(B2:B29=F1,IF(C2:C29>0,A2:A29)))

I wonder why ????

Any Idea ???

 

[hrayani]

Sorry the second formula with combine conditions is this one

=MIN(IF(AND(B2:B29=F2,C2:C29>0),A2:A29))

This is the one which is not working

 

[Aladin Akyurek]

and as is cannot deliver an array, i.e. a set values.

even in an array/processing formula with min, use an isnumber test instead of greater than zero test.

 

[hrayani]

even in an array/processing formula with min, use an isnumber test instead of greater than zero test.

Any reason for that ? i mean will the formula not give correct result ?

 

[Aladin Akyurek]

Any reason for that ? i mean will the formula not give correct result ?

Values like 1,522 are numbers and ISNUMBER returns TRUE for such values.

"-" or "hrayani" are text and ISNUMBER returns FALSE for such values, while text is always > 0 or any other number.

 

[hrayani]

Thanks very much Aladin....

this is going to help me lot.. not only in what I am doing right now but also in my future work...

 

[Aladin Akyurek]

Humayun,

You are welcome. Glad to help.