Highlight closest cell value in a table

Question

I've got a table with salary information named SalaryDataTbl, and cell K2 to the right of it has a picker where you may choose an employee. L2 checks their discipline while M2 checks their pay.

Is there a way to use an in-cell function or data validation to emphasize the SalaryDataTbl's nearest salary for their discipline? Or will a VBA solution be needed for this?

It should highlight cell G2 in the example in the image for the salary of 3750 and the discipline of programming.

Answer 1

Conditional formatting will be used to highlight cells based on the values of other cells. You'll need Excel 2021 or Excel for Office 365 for this response. Under the home tab, click "Conditional Formatting" > "New Rule" and change the setting to "Use a formula to choose which cells to format" after selecting the range D2:I18.

The formula you need to use will be

=AND(ABS(D2-$M$2)=MIN(ABS(DROP(D:I, 1)-$M$2)), $A2=$L$2)

Then, using the formatting options, choose the format you need to "Highlight" the pertinent cells, such as changing the fill colour to yellow.