A complicated problem? What is the best approach?

[Mel Smith]

I have two spreadsheets. The first has a list of teams in cells A4:A20 The second has details of match scores (consisting of two innings). What I wish to do is compare scores to those on my first spreadsheet and if a record has been broken, for a text message "New highest score" to be displayed on the second sheet in cell B25.

Details: on the first spreadsheet, the teams are listed in cells A4:A20 and the current record scores listed in B4:B20
On the second spreadsheet, the team name will be in either F10 or F15 (but not both) and their scores in either P10 or P11 (linked to the team in F10) or P15 or P16 (linked to the team in F15).

Any suggestions on how I should approach this will be greatly appreciated.

Mel

 

[RoryA]

That sounds like a simple XLOOKUP/VLOOKUP to get the current record and compare it to whichever cell has the current score in it.

 

[Mel Smith]

Hi Rory,

Thanks for your reply. Could you explain what you mean by XLOOKUP/VLOOKUP, please?

Mel

 

[RoryA]

I mean you lookup the team name - e.g.
=VLOOKUP(F10,Sheet1!$A$4:$B$20,2,false)

will lookup the team name in F10 in A4:A20 on sheet 1 and return the corresponding value from B4:B20. You can then simply compare that to the value in P10/P11 (it's not clear to me how you decide which cell has the correct value).

 

[Mel Smith]

Hi Rory,

The 'best' value could be either in P10 or P11 (1st or 2nd innings) if F10 is the team being compared or P15 or P16 if F15 is the team being compared.

Mel

 

[RoryA]

So which team does the message base itself on? Both?

 

[Mel Smith]

For example: if F10 = Warwickshire, that is no match, therefore F15 will match to one of the teams in sheet 1 range A4:A20 (and vice versa). If F10 matches to sheet 1 range A4:A20 then the value of P10 or P11 (the higher value) will be the base for triggering the message.

Mel

 

[RoryA]

Something like:

=IF(IFERROR(VLOOKUP(F10,Sheet1!$A$4:$B$20,2,false)<MAX(P10:P11),VLOOKUP(F15,Sheet1!$A$4:$B$20,2,false)<MAX(P15:P16)),"New highest score","")

 

[Mel Smith]

Hi Rory,

Perfect! Thank you so much.

Mel