oftwominds
New Member
- Joined
- Feb 14, 2022
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hi, I am trying to determine the closest guess score for a match result. I have used the index match min array formula to determine the closest guess number for team A and Team B and then conditional formatting to highlight the cell. Eyeballing I can see that name5 is the closest to guessing both team score results (boxed cells). How do I modify the formula to highlight thIs row with the guess closest to both teams' actual score rather than each team separately?
=INDEX(B7:B26,MATCH(MIN(ABS(B7:B26-B4)),ABS(B7:B26-B4),0)) outputs the nearest score number
=INDEX(A7:A24,MATCH(MIN(ABS(B7:B24-B4)),ABS(B7:B24-B4),0)) outputs the name of closest guess
=INDEX(B7:B26,MATCH(MIN(ABS(B7:B26-B4)),ABS(B7:B26-B4),0)) outputs the nearest score number
=INDEX(A7:A24,MATCH(MIN(ABS(B7:B24-B4)),ABS(B7:B24-B4),0)) outputs the name of closest guess