If multiple data values are true pick the largest one

[Mike Mc]

I'm looking for a formula that in my head starts with an IF statement, creates a table for itself and then chooses the largest value from among the positive results.

IF(A1:A5="Yes",B1:B5.....

If a user sets the value in A1:A5 to Yes, then I want the formula to grab the corresponding value from B1:B5. Should there be multiple Yes responses, I need the formula to grab the largest value from the corresponding column B values.

Yes 1
No 2
No 3
Yes 4
Yes 5

In this example column A has 3 YES responses and the corresponding values in column B are 1, 4 and 5. As 5 is the largest the formula would/should result in an answer of 5. If none of the column A values are YES, then the value returned should be 0.

Your help and input would be greatly appreciated.

[mcmahobt]

Try this; note that is is an array function and will need to be entered with CTRL+SHIFT+ENTER:

Please Login or Register  to view this content.

[Mike Mc]

Thank you Very Much! Your suggestion does indeed work