Complex nested array in Excel

Question

I'm trying to do a scoresheet lookup in Excel, and want to do it without vba.

Mainly so that the spreadsheet can be used in either Mac or Windows without issue, and also to give the score sheet a "live" feel to it with the updates happening in real time.

What I have so far is:

And using the array formula

{=MAX(IF(B2:B18=F1,C2:C18))}

I can return the best score for team 1 (93)

However although Team 1 can have as many competitors as they want, only nominated competitors scores register.

And as Felix is not a nominated competitor (only the four listed in cells F2 - F5 are)

The best score for Team 1 should be Ryan's score of 64.

So my conundrum is, I want to return the max score (like my formula already does) with the condition that the competitors name is in the Team list in column F.

I've been messing around no luck and am wondering if this is even possible without vba coding.

Many Thanks

Michael

Yes the same name would be able to appear against different teams — Michael Liew
– Michael Liew, Commented Nov 25, 2018 at 7:48
you can use IsNumber Search or add another criteria to pnuts formula. — QHarr
– QHarr, Commented Nov 25, 2018 at 8:31

Ron Rosenfeld · Accepted Answer · 2018-11-25 11:59:11Z

1

You can use this array-entered function:

=MAX(($A$2:$A$18=TRANSPOSE(F2:F5))*($B$2:$B$18=F1)*$C$2:$C$18)

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

Note that I chose to also check that the nominated competitor is in the requisite team, just in case you have two people with the same name on different teams. But that is not necessary.

NOTE: AGGREGATE function solution removed as it did not work properly.

edited Nov 25, 2018 at 11:59

answered Nov 24, 2018 at 20:51

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Liew Over a year ago

Thanks Ron, I'm using an older version of excel, but can get access to Excel 2016 so will definitely try this out when I get the chance. Out of curiosity what other formula could be used here for older versions?

Michal · Accepted Answer · 2018-11-24 19:54:50Z

0

 =MAX(IF(Scores[Team]=E1,Scores[Score])*(Scores[Competetitor]=TRANSPOSE(Team1[Team 1])))

answered Nov 24, 2018 at 19:54

Michal

7,6744 gold badges30 silver badges47 bronze badges

1 Comment

Michael Liew Over a year ago

Thanks, I can't get this to work Did you created a pivot chart or something?

QHarr · Accepted Answer · 2018-11-25 08:15:27Z

0

You could also use

=MAX(IF(ISNUMBER(SEARCH(F2:F5,A2:A18)),IF(B2:B18=F1,C2:C18)))

Enter with Ctrl+Shift+Enter

edited Nov 25, 2018 at 8:15

answered Nov 25, 2018 at 8:08

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

Michael Liew Over a year ago

Hi @QHarr That worked great with the data i provided, however when I applied it to my full data it didn't work for some reason (added image in an answer to my own questions as i'm not sure how to do it here)

QHarr Over a year ago

Also, your formula should be C2:C28 at the end. The arrays need to be the same length.

Collectives™ on Stack Overflow

Complex nested array in Excel

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related