How to lookup a value across multiple columns?

[happypiano]

I have to see if an ID number that appears on list A is already on list B. There are 5 ID types and they are each listed in their own column in list B.

For example, I have the following list A and would like a formula to find out which ones I don't have yet in list B:

List A:
Cat4
Bird2
Rabbit9

List B:

Type1	Type2	Type3	Type4	Type5
Cat1	Dog1	Bird1	Mouse1	Rabbit1
Cat2	Dog2	Bird2	Mouse2	Rabbit2
Cat3	Dog2	Bird3	Mouse3	Rabbit3
Cat4	Dog4	Bird4	Mouse4	Rabbit4

<tbody>
</tbody>

In this example, I don't have Rabbit9 yet.

Thanks!

 

[Snakehips]

happy piano,

Welcome to MrExcel.

Try like...
Sheet2

*	A	B	C	D	E
1	Cat4	*	*	*	*
2	Bird2	*	*	*	*
3	Rabbit9	Missing	*	*	*
4	*	*	*	*	*
5	*	*	*	*	*
6	*	*	*	*	*
7	Type1	Type2	Type3	Type4	Type5
8	Cat1	Dog1	Bird1	Mouse1	Rabbit1
9	Cat2	Dog2	Bird2	Mouse2	Rabbit2
10	Cat3	Dog2	Bird3	Mouse3	Rabbit3
11	Cat4	Dog4	Bird4	Mouse4	Rabbit4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(AND(A1<>"",COUNTIF(A$7:E$13,A1)=0),"Missing","") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope that helps.

 

[happypiano]

Thank you! Could you explain the IF AND formula for me in this case? I'm not very good at Excel yet and would like to understand how this works.

 

[Snakehips]

Thank you! Could you explain the IF AND formula for me in this case? I'm not very good at Excel yet and would like to understand how this works.

Glad it works.

You could just use COUNTIF(A$7:E$13,A1) and missing IDs would return 0. But so might any empty cells in list A if they have a B formula in that row.

So a more accurate return....

The IF formula..

IF (Test for some condition, This result if test is true, this result if test is false)

And formula...
Returns TRUE if all the tests (separated by comma) are TRUE

So we use AND(A1<>"",COUNTIF(A$7:E$13,A1)=0) to test if A has a value i.e. not empty AND the count of A in the second list is 0

If both TRUE then test result is TRUE and the IF returns "Missing" otherwise test result is FALSE and the IF returns "" as in nothing, empty.

Hope that helps.

Hope that helps.

Remember Google is your friend especially YouTube you will find a huge amount of tutorials out there from basics to more complex stuff.