lookup max value in length (column c)& return value in column a

[zappyzoo]

table:

Please Login or Register  to view this content.

need the activity number (column a), of the largest length(column c), if column b is between 90 to 180 & column d ="insertion".
thank you

[benishiryo]

hi zappyzoo, maybe this array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Neil_]

Probably an easier way than this, but enter as an array formula (Ctrl+shift+enter)
=SUM((D2:D7="Insertion")*(B2:B7>=90)*(B2:B7<=180)*(C2:C7=(MAX((D2:D7="Insertion")*(B2:B7>=90)*(B2:B7<=180)*(C2:C7))))*(A2:A7))

[JBeaucaire]

Ha, my version of the same idea:

=INDEX($A$2:$A$7, MATCH(MAX(($B$2:$B$7>=90)*($B$2:$B$7<=180)*($D$2:$D$7="insertion")*($C$2:$C$7)),
INDEX(($B$2:$B$7>=90)*($B$2:$B$7<=180)*($D$2:$D$7="insertion")*($C$2:$C$7), 0), 0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

http://screencast.com/t/ybYNcSAa

[Crisp Packet]

The LOOKUP could be an option! Assuming your data is in A1:E7 including headers.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Neil_]

So many ways to crack an egg. If you change C3 to the highest value, all the solutions still work except the lookup

Edit:
If you then change D3 to 'Test', the Sumproduct formula fails

[JBeaucaire]

THe INDEX array I suggested is the only one I would trust. It will give a correct answer (the first one) from the matching criteria even if there are multiple lengths that match the max length.

[sktneer]

THe INDEX array I suggested is the only one I would trust. It will give a correct answer (the first one) from the matching criteria even if there are multiple lengths that match the max length.

If you change all the sizes to less than 90 in col. B for all the insertion method in col. D, it still gives the result 1, while it should not return any value from col. A.
Right?

[sktneer]

I think only benishiryo's code works well with all the criteria if it is included with IFERROR cluase in it.

Please Login or Register  to view this content.