excelformat

Highlighting excel cells based on matching other cells that meet a separate criteria

I have a spreadsheet that identifies highlights and gives text when a set of conditions are met.

Basic data sample is (Sorry, unsure how to make it look like it does in excel with formatting and formula):

Surname,Forenames,Last Test,Previous Test,Notch,2kHz L,4kHz L,8kHz L,2kHz R,4kHz R,8kHz R
ADAMS,IAN,04/07/2022,,,15,45,60,20,45,65
ADAMS,IAN,12/02/2025,04/07/2022,,15,45,50,25,45,75
ALLEN,MARIN,26/02/2024,,Both,-10,0,-10,-10,5,-10
ALLEN,PATRICK,24/11/2016,,Left,10,25,15,0,25,25
ALLEN,PATRICK,01/07/2022,24/11/2016,,5,15,35,0,20,25
ALLEN,PATRICK,11/07/2023,01/07/2022,,5,15,40,0,5,30
ALLEN,PATRICK,03/09/2024,11/07/2023,,5,20,35,5,15,30
ALLEN,PATRICK,16/09/2025,03/09/2024,,5,20,35,5,5,40
ARCHBOLD,DAVID,09/02/2023,,Right,0,5,-5,-10,10,-5
ARCHBOLD,DAVID,20/05/2024,09/02/2023,,-5,0,0,0,10,10
ARCHBOLD,DAVID,01/05/2025,20/05/2024,,0,-5,-5,-5,0,15

Columns G and E (4KHz L and Notch) are highlighted if column G is 10 or more higher than both of columns F and H (2kHz L and 8kHz L). Using below formula in Conditional Formatting for applied to =$E:$E,$G:$G

=AND($G1>=$F1+10,$G1>=$H1+10)

The same has been done for columns I, J and K

=AND($J1>=$I1+10,$J1>=$K1+10)

I can return text in column E reflecting which cells of columns G and J are highlighted or whether both columns are affected using the following formula in column E

=IFS(AND(AND($G4>=$F4+10,$G4>=$H4+10),AND($J4>=$I4+10,$J4>=$K4+10)),"Both",
AND($G4>=$F4+10,$G4>=$H4+10),"Left",
AND($J4>=$I4+10,$J4>=$K4+10),"Right","TRUE","")

I then highlight columns A and B if column E is not blank

=$E1<>""

I would like to highlight names that match those in the highlighted cells even if E is empty.

For example I would like to highlight A6:B9 as they match the cells highlighted in A5 and B5 by the presence of text in E5.

I have googled various thoughts but they either match the cells in they match an absolute cell in Notch, or they match all duplicates in Surname or Forename irrespective of Notch. I'm afraid I'm a google excel amateur so at a bit of a loss now.

Solution

  • In cell M2 is this formula that is dragged across range M2:N20 and it creates two helper columns. Fill down and across the formula as far as required and adjust the ranges as required (e.g. A$2:A$1000 to A$2:A$20000). With legacy Excel you'll have to confirm the formula by pressing ctrl+shift+enter.

    =IFERROR(INDEX(A$2:A$1000,SMALL(IF(LEN($E$2:$E$1000)>0,ROW($E$2:$E$1000)-1),ROW(A1))),"")

    This the rule for conditional formatting of columns A and B. Adjust the rule as required (e.g. $M$2:$M$5&$N$2:$N$5 to $M$2:$M$200&$N$2:$N$200). The applies to range is =$A:$B.

    =AND($A1<>"",$B1<>"",NOT(ISNA(VLOOKUP($A1&$B1,$M$2:$M$5&$N$2:$N$5,1,FALSE))))

    It understand that you want to highlight cells A1 and B1 therefore i didn't change the existing rule and applies to range.

    cf