find highest number only if two names matched in columns?

[canadave]

Hi all,

I'm trying to set up a formula in Excel that looks at another worksheet, and shows the highest number in a column, ONLY IF a specific name is in one column AND a different specific name is in another column.

The data looks like this:

Column C on the other worksheet is one list of names (the "winners").
Column D on the other worksheet is another list of names (the "losers").
Column E on the other worksheet is the winner's score.

So, I'm trying to find the highest winning score in matches between two specific players, where one specific player was the winner. The other worksheet is named "RESULTS".

On the worksheet I'm working in, named "STATS", the names of the players I'm trying to check against are in B23 and B25. I want to find the highest winning score where B23 was the winner and B25 was the loser. I've got this formula for the cell:

{=IF(AND(RESULTS!C:C=$B$25,RESULTS!D:D=$B$23),MAX(RESULTS!E:E))}

But all that shows in my cell is "FALSE".

Can anyone shed some light on what I might be doing wrong?

Thanks!

[DonkeyOte]

As per earlier threads - avoid use of entire column references in Arrays

Using your sample - with defined names - perhaps:

Please Login or Register  to view this content.

[MarvinP]

Hi Canadave,

I have a different approach without using formulas. On your results sheet I used the K to O columns with an added column called Diff (how many points they won by). I inserted a Pivot Table next to this table in Column Q. I simply drug (dragged?) the Winner and Loser fields to the Filter Area. Then pulled the Diff field to the Summation field of the Pivot Table. Last I set the Diff to show the Max instead of Sum or Count.

Is this what you were looking for? No formulas! No CSE entry! See if this works for you in the attached.

[MarvinP]

Hi,
After looking harder at your second sheet I have 2 more suggestions on how a Pivot Table may work for you.
See the attached with two more possible Pivots on it.

[canadave]

Wow...thanks guys! I can't believe how easily it all seems to come to you...it would've taken me days to figure that out on my own, if at all.

I definitely think Pivot Tables are the way to go for much of what I'm trying to do. I wish I understood them more...I've read a bunch online, tried to teach myself how to use them, but I don't seem to be getting it (it doesn't help that most of what I've read is for earlier versions of Excel).

I think this'll be enough to go on for my purposes...thanks again!

[canadave]

A followup, if I may.

After looking at both examples more closely, I'd like to use Pivot Tables, but I think for my purposes it might be simpler just to have the few remaining cells I need to configure be calculated based on formulas. Then I'll have it done, and then I can look into using Pivot Tables in version 2.0 of the spreadsheet sometime down the road

So I went and used DonkeyOte's formula, and it worked. Simple enough!

Now, I want to find the same thing, but the MIN. In other words, when the two players play each other, I want to find the lowest score that the B23 player has scored against B25, B26, and B27 (in my example).

I used this formula as a modification of DonkeyOte's formula:

Please Login or Register  to view this content.

It works in MOST cases. However, if B23 has never lost to B26, then B23's minimum score against B26 will show as "0". That's incorrect, clearly.

It gets worse. Not only is "0" wrong, but we can't even assume that B23's lowest score is "21" in that scenario (assuming that games are normally played to 21 points). Maybe B23 won three games against B26, but they all went to OT, and the scores were 31-29, 28-26, and 27-25. In that case, the B23's lowest score against B26 should be "27."

I'm not entirely sure how to code this to allow for that possibility. Is it just a matter of adding more "IF"'s to the formula to allow for that rare possibility?

[canadave]

In fact, the problem also exists with the MAX formula DonkeyOte originally posted. If B23 has never won against B26, then the formula returns "0" as the "highest score" achieved, rather than the best score B23 achieved in his losing efforts against B26.

[daddylonglegs]

Try this formula to return the lowest losing score....or if there isn't one the lowest winning score...

=IFERROR(SMALL(IF(INDEX(_Players,0,2)=$B$23,IF(INDEX(_Players,0,1)=$B25,INDEX(_Scores,0,2))),1), SMALL(IF(INDEX(_Players,0,1)=$B$23,IF(INDEX(_Players,0,2)=$B25,INDEX(_Scores,0,1))),1))

confirmed with CTRL+SHIFT+ENTER

[canadave]

Try this formula to return the lowest losing score....or if there isn't one the lowest winning score...

=IFERROR(SMALL(IF(INDEX(_Players,0,2)=$B$23,IF(INDEX(_Players,0,1)=$B25,INDEX(_Scores,0,2))),1), SMALL(IF(INDEX(_Players,0,1)=$B$23,IF(INDEX(_Players,0,2)=$B25,INDEX(_Scores,0,1))),1))

confirmed with CTRL+SHIFT+ENTER

You know, it's always such an incredible thing to me when I try a formula and it comes out working perfectly! This worked great! Now I'll see if I can adapt it to do the same thing to fix the MAX score issue too.