Combining INDEX and MATCH with MAX

[jcswaby]

I have an existing spreadsheet with single row entries e.g. James, Jim, Tom (column R) and then some data for each (column P). My current formula that works is: INDEX('Sheet 1'!$P:$P,MATCH($A2,'Sheet 1'!$R:$R,0))

Now I have a slightly different spreadsheet with multiple row entries for James, Jim and Tom, with different data. My question is where in the existing formula do I tell it to just return the largest value for James, Jim, Tom out of all the entries? I tried MAX(INDEX('Sheet 1'!$P:$P,MATCH($A2,'Sheet 1'!$R:$R,0))) but that did not work.

Many thanks

[Tony Valko]

Maybe something like this...

Array entered**:

=MAX(IF('Sheet 1'!R2:R100=A2,'Sheet 1'!P2:P100))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If that's not even close then we'll need to see some sample data along with your expected result.

[JohnTopley]

One way

=MAX(IF($R$2:$R$1000="James",$P$2:$P$1000,""))

Enter with Ctrl+Shift+Enter

[sourabhg98]

or a non array formula with just ENTER-

Please Login or Register  to view this content.

[jcswaby]

Here's a snapshot of the data, basically the max value for Tom is 34664, Jim is 34564 and James is 435 and those entires should be displayed in B2:B4 on Sheet2.

Many thanks for your help guys.

[sourabhg98]

In B2-

Please Login or Register  to view this content.

copied down..

[Tony Valko]

This array formula** entered in B2 and copied down:

=MAX(IF(Sheet1!R$1:R$38=A2,Sheet1!P$1:P$38))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Tony Valko]

=SUMPRODUCT(MAX((Sheet1!R:R=$A2)*Sheet1!P:P))

You should avoid using entire columns as range references in the SUMPRODUCT function.

SUMPRODUCT will evaluate EVERY cell referenced. If you have 990,000 empty cells per column you're wasting resources by having to evaluate all those empty cells.

[jcswaby]

Perfect, it works now, I think I just completely over complicated it originally!

Many thanks everyone.

[Tony Valko]

You're welcome. Thanks for the feedback!