# Google Sheets - Highlight nearest value - Swimming Times

I'm banging my head against a wall trying to find a way to highlight a swimming time compared to results from past swim meets.

• Column A is my swimmers time
• Column B is the event
• Column D is the time for a few places within that event
• Column E-W is the time result for that place of that event per college conference (row 1 = conference)

Goal - Highlight the nearest place my swimmers time would put them in each conference.

Example - My swimmers 50 Free time of 24.85 would put them closest to 32nd place in the BIG12, 1st place in the SAAC and 9th place in the PCSC.

A copy of my data is below. Any help would be greatly appreciated!

Try this formula :

`=if(isna(query({\$A\$2:\$B\$64,indirect(address(2,vlookup(L\$1,\$R:\$S,2,false ),1,1)&":"&address(counta(\$B:\$B),vlookup(L\$1,\$R:\$S,2,false ),1,1))}, "Select Col1 where Col2 = '"&\$K2&"' and Col3 <= '"&\$J2&"' order by Col3 desc limit 1 ",)),1,query({\$A\$2:\$B\$64,indirect(address(2,vlookup(L\$1,\$R:\$S,2,false ),1,1)&":"&address(counta(\$B:\$B),vlookup(L\$1,\$R:\$S,2,false ),1,1))}, "Select Col1 where Col2 starts with '"&\$K2&"' and Col3 <= '"&\$J2&"' order by Col3 desc limit 1 ",))`

Refer the screenshot below: copy the formula into cell L2, and copy it across to column P, and drag down for as many swimmer's times as are needed.

LOGIC

• Conference Names/Columns Table

• Columns S and T display the names of the conferences and the column number in which the conference results are found
• `=transpose(C1:G1)` = Column names
• `=arrayformula(row(B2:B6)+1)` = Column number of the relevant conference results (BIG12=C=3, SAAC=D=4, etc)
• The column numbers are used to create dynamic ranges for the query
• Query Range

• `\$A\$2:\$B\$64` = Place and Event
• `indirect(address(2,vlookup(L\$1,\$R:\$S,2,false ),1,1)&":"&address(counta(\$B:\$B),vlookup(L\$1,\$R:\$S,2,false ),1,1))` = relevant Conference Times
• `INDIRECT()` returns a range contained in a string
• `ADDRESS()` returns a cell reference as a string; in this case two cells are joined by a colon `&":"&` to create a range
• `address(2,vlookup(L\$1,\$R:\$S,2,false ),1,1)&":"&address(counta(\$B:\$B),vlookup(L\$1,\$R:\$S,2,false ),1,1)` creates a dynamic range
• part1= returns the starting cell of the relevant conference results by taking the conference name in L1:P1 and referencing the Conference Names/Columns Table to yield the column number.
• part2= returns the ending cell of the relevant conference results by counting the number of rows in the Events column (B); the column number is the same as part1.
• Query Criteria

• `Select Col1 where Col2 = '"&\$K2&"' and Col3 <= '"&\$J2&"' order by Col3 desc limit 1`
• Col1 = Place (Column A)
• `where Col2 = '"&\$K2&"' and Col3 <= '"&\$J2&"'` combines matching the Conference Event to the swimmer's event (in Cell K2) AND the the conference time is less than or equal to the swimmer's time (in Cell J2).
• IF(ISNA()...

• Where the swimmer's time is less than a Conference first place time (such as 23.91 compared to SAAC first place of 24.46), the query returns #N/A. `IF(ISNA()` is used to assign a first place result in those cases.

Sample data

Note: MVCC 50FREE last place time was an error; must be greater than other times in the race.