If list B is a subset of list A, each entity of list A has a value, find corresponding value of list B and then find max; all this in one step

[Taj_Uddin]

I have a list of nodes (List B in col A) and corresponding temperature in Col B. List B in Col D is a subset of list A. In cell E2, I used the Index+Match formula to find the corresponding temperature. The formula is =INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)). I copied this formula down to cells E3 and E4. Cell F2 is the max of values in Col E.​

A B C D E F 1 Node # (List A) Temperature (Deg. F) Subset Node # (List B)​ <strike style="border-collapse: collapse; width: auto;"></strike> Temperature (Deg. F) Max 2 5 90 3 85 95 3 3 85 9 95 4 8 67 5 90 5 9 95 6 2 97 <tbody style="border-collapse: collapse; width: auto;"> </tbody>
What I want to do: I want to find the same max value in cell F2 without doing the step of Col E, i.e., use only one equation that will match the nodes in Col D with that of Col A and find the corresponding temp from Col B when match and then find the max of those matches. I am using MS Office 2013. Also, I want to avoid VBA. Can I do this in one step? Why I want to do this: Above example shows a few nodal temperature of a particular structure for a particular environmental condition. I have 21 structure each of which has thousands of nodes. Also, I have 41 environmental conditions. If I do the method as shown above then I will end up with a big database. But if can skip the step done in Col D, then it will be a very small database.

<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" span="2"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" span="3"></colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
</tbody>

 

[Eric W]

Welcome to the forum.

Try:

=MAX(IF(ISNUMBER(MATCH(A4:A8,D4:D6,0)),B4:B8))

confirmed with Control+Shift+Enter, not just enter.


Edit:

This works without the Control+Shift+Enter:

=AGGREGATE(14,6,SIGN(MATCH(A4:A8,D4:D6,0))*B4:B8,1)

 

[Taj_Uddin]

Works perfect. Thanks for the great help!!!

 

[Eric W]

You're welcome!