referencing data set against known good and bad items

[artemis_f0wl]

Hello again! Sorry if the post is not titled correctly. If I knew how to ask google for this, I probably wouldn't be bothering people who have more important things to do with their time than help a newb!

I am taking a second shot at asking this after doing more research and creating a sample woorkbook which is attached. I am trying to create an shopping cart inventory system of sorts. The workbook is comprised of 3 worksheets (Cart, whiteList, and blackList). The Cart will be the container for items to be approved on our shopping trip. As we wander the store, we toss items in the cart (more accurately, the cart will be a complete shopping list that will be pasted from a text file). Once the cart is populated, the magic needs to happen.

As our cart is populated, I now want to have each item in the cart cross-referenced with each item on both the whiteList and the blackList. If an item appears on the whiteList, it is approved and given a rating of 0. If it appears on the blackList, the item is given a rating of 1. If the item is not on either list, the "Manual?" column needs to be populated with an indicator that the shopper needs to research further to decide if the item should be added to the whiteList or the blackList.

Eventually, every item the shopper would ever buy will be categorized as allowed or unallowed. It would be helpful to identify a way to automate the addition of manually identified items to the appropriate list, but I'd be happy with adding them manually if I can just understand how to accomplish the main goal of qualifying each item on the given list.

Thanks in advance for any help you can offer.

[JBeaucaire]

This should do it all in one cell:

=IF(ISNUMBER(MATCH(A2,whiteList!A:A,0)),0,IF(ISNUMBER(MATCH(A2,blackList!A:A,0)),1,"Manual?"))

If you must use two columns, then use these two formulas:

B2: =IF(ISNUMBER(MATCH(A2,whiteList!A:A,0)),0,IF(ISNUMBER(MATCH(A2,blackList!A:A,0)),1,""))
C2: =IF(AND($A2<>"",$B2=""),"CHECK","")

[artemis_f0wl]

I must apologize for my ignorance, but I am truly inept when it comes to excel!

If this can be done in one cell, I see no reason to make it more difficult. I took your suggestion:

Please Login or Register  to view this content.

I tried this in multiple areas, but I must be missing something. When I enter the above code in column B of the Cart, it changes ALL items to a value of 1. This is not the desired effect, as some items should be given a value of 0 to indicate they are in fact on the white list!

I tried inserting this into column A but that was obviously the wrong thing to do as all items were then given a value of 0.

Please help me understand more clearly.

[JBeaucaire]

Here's an attachment, perhaps you're simply suffering from typos.

[artemis_f0wl]

Great information, thank you for the clarification!

I will be experimenting with this further. I really appreciate the input.