# Finding items in one list that appear in another

#### egoodman

##### New Member
My problem: I have a very large list of items that I had to break into two spreadsheets. The first spreadsheet (spreadsheet #1) has 55,931 lines and three columns the second one (spreadsheet #2) has 64,837 lines and three columns. I have a third sheet (spreadsheet #3) that consists of 1160 lines and one column. I would like to identify everytime an item in sheet #3 appears in Column B of spreadsheets 1 and 2 by having them turn the background yellow. For instance if spreadsheet 3 was a list of automobiles and spreadsheets 1 and 2 was a list of all vehicles I would like to identify everytime an automobile is located in spreadsheets 1 and 2 that appears on sheet 3 by turning the background yellow.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

##### MrExcel MVP
On 2002-10-08 12:00, egoodman wrote:
My problem: I have a very large list of items that I had to break into two spreadsheets. The first spreadsheet (spreadsheet #1) has 55,931 lines and three columns the second one (spreadsheet #2) has 64,837 lines and three columns. I have a third sheet (spreadsheet #3) that consists of 1160 lines and one column. I would like to identify everytime an item in sheet #3 appears in Column B of spreadsheets 1 and 2 by having them turn the background yellow. For instance if spreadsheet 3 was a list of automobiles and spreadsheets 1 and 2 was a list of all vehicles I would like to identify everytime an automobile is located in spreadsheets 1 and 2 that appears on sheet 3 by turning the background yellow.

Sheet1: Let column B from B2 on house vehicles.

Sheet2: Let column B from B2 on house vehicles.

( 1.) Select all the automobile cells in Sheet3.
( 2.) Go to the Name Box on the Formula Bar.
( 3.) Type List and hit enter.
( 4.) Select all the vehicle cells in column B in Sheet1.
( 5.) Activate Format|Conditional Formatting.
( 6.) Choose Formula Is for Condition 1.
( 7.) Enter the following in the white box:

=MATCH(B2,List,0)

( 8.) Activate the Format button.
( 9.) Choose yellow on the Patterns tab.
(10.) Click OK, OK.
(11.) Repeat the steps 4 to 10 for Sheet2.

Replies
2
Views
109
Replies
3
Views
216
Replies
4
Views
165
Replies
6
Views
96
Replies
1
Views
76

1,148,334
Messages
5,746,158
Members
423,995
Latest member
excelbloggs

### 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.

### Which adblocker are you using?

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

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