VLOOKUP/HLOOKUP/XLOOKUP novice question

[bzenner]

Hello all, 

First, thanks in advance for any help. 

Running Microsoft Excel for Mac v 16.56, MacOS Mojave 12.1

I am doing some scorekeeping for a gymnastics team, using the attached spreadsheet. My question focuses around the ALL AROUND section, range A40:I44. In that group, I have VLOOKUP formulas to look up the name and values for the BARS, BEAM and FLOOR events in the spreadsheet. That was easy for me to do. 

However, the VAULT is a little tougher. That's because in gymnastics, a gymnast will perform once on an event, and their score is averaged by the two judges...however on the vault, a gymnast has two attempts. So in the ALL AROUND section at the bottom, what I need to have is a formula that will lookup Jenny's name in the Vault group, look at her two scores (D3 and D4) and return the LARGER (or MAX) of the two scores. In Jenny's case, 8.500 in cell B42. For Jasmine, it should return the value 8.500 from the D5 and D6 scores in cell B43. For Emily, it should return in cell B44 the value 8.200 from the scores in D7 and D8. 

This is where I don't know how to write the formula. I believe it's an XLOOKUP of some kind, with MAX somewhere in there, but I have no idea where to start or how to write it.

8.5008.5008.2008.1008.3008.5009.1008.9008.7007.7007.9008.10024.900025.100025.3000

VAULT
Name	Score	Avg.
Jenny	7.000	8.000	7.500
8.000	9.000	8.500
Jasmine	9.000	8.000	8.500
8.000	8.400	8.200
Emily	8.200	8.200	8.200
8.000	8.000	8.000
UNEVEN BARS
Name	Score	Avg.
Jenny	8.000	8.200	8.100
Jasmine	8.200	8.400	8.300
Emily	8.400	8.600	8.500
BEAM
Name	Score	Avg.
Jenny	9.000	9.200	9.100
Jasmine	8.800	9.000	8.900
Emily	8.600	8.800	8.700
FLOOR EXERCISE
Name	Score	Avg.
Jenny	7.600	7.800	7.700
Jasmine	7.800	8.000	7.900
Emily	8.000	8.200	8.100
ALL AROUND
Name	VAULT	BARS	BEAM	FLOOR	TOTAL
Jenny	?????	8.100	9.100	7.700
Jasmine	?????	8.300	8.900	7.900
Emily	?????	8.500	8.700	8.100

Anyone have any suggestions or help for me???? THANK YOU!

Brad

[#2]

@bzenner I copied the table into an empty sheet and moved a few cells around to arrive at what I believe your real sheet looks like. Why don't you add a column (E) in the VAULT section that calculates the MAX of the two Average scores for each gymnast? Than you can use VLOOKUP or XLOOPUP just like you did for the other disciplines.

[#3]

@Riny_van_Eekelen Duh that makes sense, thanks for the idea! I thought i was going to have to do some complicated formula at the bottom of the sheet. Appreciate it!
 Brad