Highlight data with vlookup

You would need 1 rule for each condition, and yes, you would been CF and probably vlookup or match

ok i tried them but they dont highlight the correct data.

With the rule for both lists I get items such as these highlighted:

770186 - doesnt appear in either list
751054 - only in TOP-75
751056 - appears in both

your rule:
=AND(ISNUMBER(MATCH($A2,'TOP-75'!$A$1:$A$74,0)),ISNUMBER(MATCH($A2,List1!$A$2:$A$107,0)))

my rule:
=AND(ISNUMBER(MATCH($A2,'TOP-75'!$A$1:$A$74,0)),ISNUMBER(MATCH($A2,List1!$A$2:$A$107,0)))

your file:
has the Conditional Formatting applies to A2-A8 (image attached)

my file:
hast the Conditional Formatting applies to A2-A1013 (image attached)

So they look identical to me, the sheets are named the same.

Im trying to upload the file but I cannot because its 1.21MB which exceeds the 1MB limit.

Thanks, fixed it.

Ok so now the only other thing I would add would be to identify the items in TOP-75 that are in List1. So if i understood your formulas correctly, it should be:

=ISNUMBER(MATCH($A1,List1!$A2:$A107,0))

Got it! So now its more a matter of logic that I need to fix in my head before going on.

The long data sheet is all the catalog products available in a company. The List1 sheet is what the purchasing manager wants to purchase.
And the TOP-75 sheet are the identified items in the entire catalog which have the most consistent sales on a monthly basis out of all 1119
products in the catalog.

STEP1:
So the first conditional format which marks the items in the catalog that appear in both List1 & TOP-75, is a way to draw attention to the
items in the catalog that the manager must further analyze in order to determine how much of those items to purchase.

STEP2:
The second conditional I just added based on your formulas alerts the manager to which items from the TOP-75 list, which is the list of
items that should almost always be ordered since they are the best-selling items, are being ordered and so the other TOP-75 items which
are not highlighted must be further analyzed in order to determine which TOP-75 may have been overlooked by her List1.

In this step it would be helpful for me to add a way to quickly count and identify or group those items. So for example in this file,
17 of the TOP-75 items are marked in red. This means the manager must go analyze the 17 but then also the manager must look at the other
58 items to see why they weren't included in List1. Is there a way to take that list and group all marked and non-marked items?

I tried selecting the column and clicking on Filter which adds the little dropdown to A1, and I click that to set a filter to Color > Rose
it does work but for some reason leaves A1 where it is, which happens to be a non-rose marked cell, then all the rose marked cells, and then
the remaining non-rose marked cells. It is a small thing, but im wondering why it leaves that first A1 cell out of sorts?

STEP3:
The final step seems to me like it would be to look at all other items in the catalog which were not identified as TOP-75 or as List1, which
may present a need for ordering which List1 and TOP-75 missed. This would be the case of the manager reviewing the original full 1119 item
list and identifying an item such as A99, A101, A104 and others even like A483 and A487. The manager would use some other column in the
dataset to identify that item as important and present it as an option. Eventually those might even be items that appear in TOP-75 but
that hadn't been marked as required in List1.

I know this is not precisely an Excel question but I think its gotta be a common question from purchase managers, how to take items in their
catalog and cross reference them to items they are presented by Sales for example as potential re-order candidates and then purchasing
analyzes those items further.