Explanation
Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, cell B3 in this case.
To highlight matching rows, we use this logical expression:
$B4=$K$5
The reference to B4 is mixed , with the column locked and row unlocked, so that only values in column B are compared to the country in cell K5. The reference to K5 is absolute , to prevent changes when the conditional formatting is applied to every cell in the range B4:H9. In the example shown, this logical expression will return TRUE for every cell in a row where country is “Korea”.
To highlight matching columns, we use this logical expression:
B$4=$K$6
The reference to B4 is again mixed. This time, the row is locked and the column is relative, so that only values in row 4 are compared to the month value in cell K6. The reference to K6 is absolute, so that it won’t change when the conditional formatting is applied to every cell B4:H9. In the example shown, this logical expression will return TRUE for every cell in a column where row 3 is “Apr”.
Because we are triggering the same conditional formatting (light yellow fill) for both rows and columns, we wrap the logical expressions above in the OR function. When either (or both) logicals return TRUE, the rule is triggered and the formatting is applied.
Highlight intersection only
To highlight the intersection only, just replace the OR function with the AND function:
=AND($B4=$K$5,B$4=$K$6)
Explanation
The SEARCH function returns the position of the text you are looking for as a number (if it exists). Conditional formatting automatically treats any positive number as TRUE, so the rule is triggered whenever SEARCH returns a number. When SEARCH doesn’t find the text you’re looking for, it returns a #VALUE error, which conditional formatting treats as FALSE.
Using the ampersand (&) we are concatenating all values in each row together and then searching the result with SEARCH. All addresses are entered in “mixed” format, with the columns locked and the rows left relative. Effectively, this means that all 4 cells in each row are tested with exactly the same formula.
Using other cells as inputs
Note that you don’t have to hard-code any values that might change into the rule. Instead, you can use another cell as an “input” cell so you can easily change it later. For example, in this case, you could name cell E2 “input” and rewrite the formula like so:
=SEARCH(input,$B4&$C4&$D4&$E4)
You can then put any text value in E2, and the conditional formatting rule will respond instantly, highlighting rows that contain that text. See the video link below for a more detailed description.
Case sensitive option
If you need a case-sensitive option, you can use the FIND function instead of SEARCH like so:
=FIND(input,$B4&$C4&$D4&$E4)
The FIND function works just like SEARCH but matches the case as well.
