Conditional formating nightmare

All -

New here. This is my first post and it's because I'm stuck and confused.

Here is what I have:

I have:
1) first table (sheet 1) shows each individual sales item in its own row. Columns at the top illustrate various features, with the most important one (Authorization) showing "A" for authorized, "O" for Optional, "U" for Unauthorized.
2) a second table (sheet 2) shows each store in it's own row, with the items as columns. Sales data for each item, if it exists, is in the corresponding cell.

What I need is to conditionally format on the second table for each item's status at each store.
I need to show:
1. If the item is authorized ("A") but no sales (essentially a blank cell)
2. If the item isn't authorized ("U") either with or without sales
3. If the item is optional ("O") either with or without sales

My issue is in the conditional rule formatting. I believe that I will need to use an IF and VLOOKUP but having trouble orienting it well.

Ideas?

Hi - can you post a small sample of your file?

Here is what I am working on (a very simplified version, with both tables on one sheet.

While having F5:I9 selected, go to Conditional formatting and select Manage Rules, New Rule. Enter this formula in the formula box:
=AND(XLOOKUP($B5&F$4,$M$5:$M$24&$N$5:$N$24,$P$5:$P$24,,0)="A",F5="")
Fill-in the color you want.
Then add this formula:
=XLOOKUP($B5&F$4,$M$5:$M$24&$N$5:$N$24,$P$5:$P$24,,0)="O"
and add your formatting, then:
=AND(XLOOKUP($B5&F$4,$M$5:$M$24&$N$5:$N$24,$P$5:$P$24,,0)="U",F5>0)
and add your formatting.

See attached file.

Brb - looking at your response and looking at the file. Might have a follow up for you.

Greg -

Thank you!
I am going to spend a bit of time on this so I understand the logic string but I think I get it. Hadn't considered using AND or XLOOKUP. Game changers!
First time ever on this forum and I know I'll be back often.
Just took a new job and its Excel all day long. I may not post all the time but I'll be here learning a lot!

Again, Thanks.

Glad it helped and thanks for the rep!

Originally Posted by Gregb11

Glad it helped and thanks for the rep!

I stayed up working all night and am just now climbing into bed.

I had fortune with my actual file with the “U” items but the “O” didn’t work and the “A” only worked for one of the items. When I wake in a few hrs, I’ll send back over a snippet. Maybe we can figure out where the issue is at. I’m more than intrigued right now!