How to lookup a value across multiple columns?

happypiano

New Member
Joined
Oct 24, 2016
Messages
2
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Type1[/TD]
[TD]Type2[/TD]
[TD]Type3[/TD]
[TD]Type4[/TD]
[TD]Type5[/TD]
[/TR]
[TR]
[TD]Cat1[/TD]
[TD]Dog1[/TD]
[TD]Bird1[/TD]
[TD]Mouse1[/TD]
[TD]Rabbit1[/TD]
[/TR]
[TR]
[TD]Cat2[/TD]
[TD]Dog2[/TD]
[TD]Bird2[/TD]
[TD]Mouse2[/TD]
[TD]Rabbit2[/TD]
[/TR]
[TR]
[TD]Cat3[/TD]
[TD]Dog2[/TD]
[TD]Bird3[/TD]
[TD]Mouse3[/TD]
[TD]Rabbit3
[/TD]
[/TR]
[TR]
[TD]Cat4[/TD]
[TD]Dog4[/TD]
[TD]Bird4[/TD]
[TD]Mouse4[/TD]
[TD]Rabbit4[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
happy piano,

Welcome to MrExcel.

Try like...
Sheet2

*ABCDE
Cat4****
Bird2****
Rabbit9Missing***
*****
*****
*****
Type1Type2Type3Type4Type5
Cat1Dog1Bird1Mouse1Rabbit1
Cat2Dog2Bird2Mouse2Rabbit2
Cat3Dog2Bird3Mouse3Rabbit3
Cat4Dog4Bird4Mouse4Rabbit4

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top