Conditional formatting - How To Highlight Rows Based On Multiple Cell Values

[esoto56]

I am wanting to highlight the used locker numbers located in columns D:G (North and South side lockers) once the matching locker number is entered into column A but only if it also matches the correct info in column B (North or South).

The locker numbers in columns D:G are all the lockers we have in the warehouse, the locker numbers in column A are what is currently assigned. Essentially I want to see what lockers are available at a glance.

The North and South break rooms both have lockers that are numbered the same. I have not entered the information for the South lockers.

I have tried the =AND(E11:F37=B3:B61,C3:C61=E9) but nothing happens.

I’ve even flopped the cells around =AND(B3:B61=E11:F37,E9=C3:C61) and still nothing.

[Fluff13]

How about
for cols D:E
=IFERROR(INDEX($A$3:$B$61,MATCH(D11,$A$3:$A$61,0),2)=$D$9,FALSE)
and for cols F:G
=IFERROR(INDEX($A$3:$B$61,MATCH(F11,$A$3:$A$61,0),2)=$F$9,FALSE)

[Tsjallie]

Create these Named Ranges:
- NorthTop = D11:D37
- NorthBottom = E11:E37
- SouthTop = F11:F37
- SouthBottom = G11:G37
Select A2:B61
Add this Conditional Formatting formula:

Please Login or Register  to view this content.

[esoto56]

IT WORKS!!! *Virtual* High Five!!!!!!!

Thanks a million

Fluff13

and also thank you Tsjallie for your input!

[Fluff13]

Glad to help & thanks for the feedback

[esoto56]

I've noticed a little issue with this formula, if the same number is entered in column B but with a different location in column C, the formula will choose one or the other.

[Tsjallie]

Match finds the first the occurence of a value.
So if you have locker 1 North in row 3 and locker 1 South in row 4, it will only find locker 1 North.

Instead of

Please Login or Register  to view this content.

use this one

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

use this one

Please Login or Register  to view this content.