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

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,

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

"Francisco Rodriguez" wrote:

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,

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

Francisco

"Eduardo" wrote:

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

"Francisco Rodriguez" wrote:

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,

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
---
"Francisco Rodriguez" wrote:
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"?

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


"Francisco Rodriguez" wrote in message
...
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,

Google is your friend:
http://www.cpearson.com/excel/ListFunctions.aspx
--
David Biddulph

"Francisco Rodriguez" wrote
in message ...
I'm sorry, I don't know where you are sending me to look at. Please
clarify.

Francisco

"Eduardo" wrote:

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

"Francisco Rodriguez" wrote:

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,

Hi,
sorry I didn't copy the web address here it is

http://www.cpearson.com/excel/ListFunctions.aspx


"Francisco Rodriguez" wrote:

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

Francisco

"Eduardo" wrote:

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

"Francisco Rodriguez" wrote:

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,

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

"Max" wrote:

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
---
"Francisco Rodriguez" wrote:
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"?

Welcome, glad it got you going there
--
Max
Singapore
"Francisco Rodriguez" wrote
in message news
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