Comparing values in 3 lists

[Jonleech]

I have a list of values lets say its
A
B
C
D

and I got to compare with 3 lists
list 1 : A
G
H

list 2 : B
C
Z

list 3 : D
Y
W

I was using COUNTIF to compare the values and though I managed to compare the list of values to one list, I can't seem to combine it with a OR function to check if this value is not in list 1 is this value in list 2 and so on.

The desired result is

A list1
B list2
C list2
D list3

i will have another column to tell me which value is in which list.

Please help ..

 

[Somendra Misra]

Hi,

See below, hope this will help.

This is your data:



Here's formula in column J entered as an array formula, so must be confirmed with Ctrl+Shift+Enter. DON'T put {} around formula manually, excel will put them as you will press Ctrl+shift+Enter.


Another option in Column K, same array formula option:



Or another non-array option for Excel 2010+

for above data layout:

in K4 and copy down:

=INDEX($L$3:$N$3,,AGGREGATE(15,6,(COLUMN($L$3:$N$3)-COLUMN($L$3)+1)/(I4=$L$4:$N$6),1))



Regards,

 

[Jonleech]

I went about another way

lets say the values in my list is in a table data[VALUE]

I use an IF and COUNTIF

=IF(COUNTIF(list1, [@VALUE])=0, IF(COUNTIF(list2,[@VALUE])=0, IF(COUNTIF(list3, [@VALUE])=0, "-", "list3"), "list2"), "list1")

thanks for your prompt reply :D

 

[Somendra Misra]

@Jonleech

Nice formula, but if you have hundred of list than changing the range in my suggestion will fetch you the result where as you can not go on adding the list in your formula.

I hope the list will not contain duplicates.

Regards,