Last cells with condition

[Yoni17]

Hi,

I'd like to find the lowest price on all last Green cells
When it is Red, stop calculating.
In the file attached, the formula should give the price : 200

Thank you

[mehmetcik]

Enter this formula into any cell using Ctrl Shift Enter.

=MIN(IF(INDIRECT("A" & MATCH("Red",A1:A6,0) & ":A6")="Green",INDIRECT("B" & MATCH("Red",A1:A6,0) & ":B6"),9^99))

[tim201110]

=MIN(INDEX(B:B,AGGREGATE(14,6,ROW(A2:A600)/(A2:A600<>"Green")/NOT(ISBLANK(A2:A600)),1)+1):A600)

[Yoni17]

Thank you for your reply.

It returns #VALUE! (on the A2:A6="Green")

[mehmetcik]

Enter this formula into any cell using Ctrl Shift Enter.

Press and hold ctrl, press and hold shift, press enter.

=MIN(IF(INDIRECT("A" & MATCH("Red",A1:A6,0) & ":A6")="Green",INDIRECT("B" & MATCH("Red",A1:A6,0) & ":B6"),9^99))

[Yoni17]

=MIN(INDEX(B:B,AGGREGATE(14,6,ROW(A2:A600)/(A2:A600<>"Green")/NOT(ISBLANK(A2:A600)),1)+1):A600)

Thanks.
It returns 0

[Yoni17]

Enter this formula into any cell using Ctrl Shift Enter.

Press and hold ctrl, press and hold shift, press enter.

=MIN(IF(INDIRECT("A" & MATCH("Red",A1:A6,0) & ":A6")="Green",INDIRECT("B" & MATCH("Red",A1:A6,0) & ":B6"),9^99))

Still #VALUE!
Am I doing something wrong?
Attached.

[mehmetcik]

Yes.

you did not use ctrl Shift Enter

[Yoni17]

Yes.

you did not use ctrl Shift Enter

I did, nothing happened.
Appreciate it. thanks a lot

[Yoni17]

I tried it with different values, when you change A5 into Red, the formula calculates wrong.
Attached.
Thanks

[Bo_Ry]

Please try

=MIN(INDEX(B:B,MATCH(2,INDEX(1/(A1:A999<>"Green")/(A1:A999<>""),))+1):B999)

[Yoni17]

Doesn't work when the last cell is Red

[Bo_Ry]

Please try with Ctrl+Shift+Enter

Please Login or Register  to view this content.

[Yoni17]

Looks good. that's a long, complicated and impressive formula.
Thanks!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.