Look up if a value from an array "A" is in either array "B" or "C"

  • Thread starter Francisco Rodriguez
  • Start date
F

Francisco Rodriguez

Hello:

I got a consolidated list of items, "C", which is the latest update of items
I need to analyze. In the past I had received two previous lists "A" and B"
that I analyzed already but are now contained within list "C", along with new
items I have yet to analyze.

I want to be able to check from list "C" the items contained on either list
"A" or "B" or neither, and return a specific string depending on the outcome,
so that I know exactly which items I have left to analyze.

I tried an IF statement performing a VLOOKUP function along with ERROR.TYPE
against lists "A" and "B", to identify the items that are not on either list
"A" or "B". However, if the item IS on either one I get another error "#N/A"
that just does not look elegant. This is happening because if the VLOOKUP
finds the item on "A" or "B", then the ERROR.TYPE returns "#N/A" and the IF
statement can't be evaluated, hence it returns #N/A.

Is there a way to get specific strings both when an item is and when it
isn't on lists "A" or "B", without returning "#N/A"?

I appreciate your support. Thanks in advance.

Sincerely,
 
E

Eduardo

Hi,
take a look to CPearson web, go to the middle of the page where it says
Extracting Elements from one list not on another list
 
F

Francisco Rodriguez

I'm sorry, I don't know where you are sending me to look at. Please clarify.

Francisco
 
M

Max

Perhaps try this simple formulas play to extract items in C not in A or B
Assume your C list is in A2 down
and your A and B lists are pasted in B2 down, C2 down
Put in D2:
=IF($A2="","",IF(SUM(COUNTIF(B:B,$A2),COUNTIF(C:C,$A2))=0,"x",""))
Copy down to the last row of data in col A. This flags items in C not in A
or B. Apply autofilter on col D, choose: x to easily isolate. Copy n paste
the filtered col A elsewhere.
--
Max
Singapore
---
:
I got a consolidated list of items, "C", which is the latest update of items
I need to analyze. In the past I had received two previous lists "A" and B"
that I analyzed already but are now contained within list "C", along with new
items I have yet to analyze.
Click to expand...
 
B

Bernie Deitrick

Francisco,

For each item in C, use a formula like

=IF(NOT(ISERROR(MATCH(D2,ListA,FALSE))),"In A",IF(NOT(ISERROR(MATCH(D2,ListB,FALSE))),"In B","New
Item"))

I have assumed you have names ListA and ListB - or you can use cell addresses, like $A$2:$A$1000

HTH,
Bernie
MS Excel MVP
 
F

Francisco Rodriguez

Thanks Max! The COUNTIF function was very helpful. I used on two new columns
that checked each whether the items on file "C" were on file "A" or "B"
respectively. Then I created another column with an IF contaistatement that
returns an "x" for items that were NOT on "A" AND "B". Filtering for the "x"
gives me my desired list. Thanks again!

Francisco
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Conditional sum, when condition has multiple values 3
Look Up Multiple Values And Returning One Value 3
Looking up whether one item has a certain matching item 6
Look up one value and return multiple corresponding values 2
Looking up name with 2 criteria 5
How to supress #N/A in an array formula. 5
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY 3

Top