# How do i find the max of two values from index match max of a column

1. ## How do i find the max of two values from index match max of a column

I am newbie to excel. I have this following data in excel sheet.

swQj9.png

http://i.stack.imgur.com/swQj9.png

I want to have the value of G11 returned 27, as the value of its corresponding cell in column D, which is D11 matches to E8, and E9, which then correspond to the values of 14 and 27 in column J. Could someone tell me how to return a match value when I have two match situations and I need the maximum one. I am using the following formula.

=IF(D11=1,0,INDEX(\$H\$4:\$H\$13,MATCH(D11,\$E\$4:\$E\$13,0),1))

I know if I use

=IF(D11=1,0,INDEX(\$H\$4:\$H\$13,MATCH(D11,\$E\$4:\$E\$13,0)+1,1))

it returns me with the value 27 or greater of the both values but it will not work if I have three values and I want the maximum.  Register To Reply

2. ## Re: How do i find the max of two values from index match max of a column

You can use an Array Formula

Formula:  `Please Login or Register  to view this content.`

committed with Ctrl-Shift-Enter rather than just Enter

This will be confirmed as an array formula with curly brackets:

Formula:  `Please Login or Register  to view this content.`

Regards, TMS  Register To Reply

3. ## Re: How do i find the max of two values from index match max of a column

Sorry, the full formula is:

Formula:  `Please Login or Register  to view this content.`

committed with Ctrl-Shift-Enter rather than just Enter

Regards, TMS  Register To Reply

4. ## Re: How do i find the max of two values from index match max of a column

Thank you TMShucks for taking time to help me in the matter. However, I am sorry but I could not implement the formula to my advantage. It is only because I know very little. Here is the link for the file I have been working on http://goo.gl/V9VYfa. What I really want to is to return in cell G11 the max value in D11 when it is equal to any of the cells in column E, when equal the return value would be the corresponding value in column H. As in the case of D11 there are two matching cells E8, and E9. I want to return the max value among these.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 