Highlight closest cell value in a table

0 votes

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.

Salary Details

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.

Feb 18, 2023 in Others by Kithuzzz
• 38,020 points
334 views

1 answer to this question.

0 votes

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.

answered Feb 18, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
405 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,720 points
694 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,720 points
850 views
0 votes
1 answer

VBA - user input of row value to use in a cell range

Use this: Range("C" & c & ":I" & ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,720 points
422 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,606 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,890 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
935 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,479 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,720 points
760 views
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,720 points
784 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP