# Comparing two columns from one sheet with two columns from another sheet

#### mommacude

##### New Member
I've been searching but can't find something that matches exactly what I'm trying to do.

I have sheet which contains a list of hotels. These hotels are repeated and black out dates are in another column sort of like so:

Charlotte - Airport South 05/26/17
Charlotte - Airport South 05/27/17
Charlotte - Airport South 05/28/17
Charlotte - Airport South 06/30/17
Charlotte - Airport South 07/01/17
Charlotte - Airport South 07/02/17

I have a second sheet with similar data.

I am trying to determine if THIS hotel with THESE blackout dates is present in both files. So in other words does this hotel's dates from file "A" exist in file "B"?

I tried vlookup but that's returning only the first date, which doesn't help. I considered conditional formatting, but I'm having trouble with that. Can anyone help? Thanks so much!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### GTdiscus

##### New Member
I have a feeling that someone else might have a better way to do this that's more succinct/efficient. But I would personally use a SUMPRODUCT to tackle this. Let's just call each sheet as 1 and 2. You will add a column to sheet 2 with just a bunch of 1s all the way down. In sheet 1 you'll add a column with the following equation:
=SUMPRODUCT(--(A1:A10="HOTEL"),--(B1:B10="DATE"),C1:C10)
The column with the hotel in sheet 2 will be A (range of 1 to 10 in example) and the dates in sheet 2 are B1:B10. You will likely want to lock the references with a \$ to be \$A\$1:\$A\$10, etc. The new column of 1s is represented here as C1:C10. It will essentially just check to make sure both conditions are met and sum the cell of 1s where it is true. So, if you have a match for your row in sheet 1 with that of 2...you'll get a 1 in your cell of sheet 1. If you get a 0...no match. If you get more than 1...you had more than one instance of the same data popping up in sheet 2 (this means the reverse could be true too (but isn't checked for in this example)...you could have more than one instance of the same hotel/date in sheet 1).

Hope this makes sense!
Scott

#### GTdiscus

##### New Member
I forgot to mention...the quotations aren't likely needed in your problem...you'll just have your cell from sheet 1 in that place. Yours will likely look more like:
=SUMPRODUCT(--(\$A:\$A=A1),--(\$B:\$B=B1),\$C:\$C)

you'll likely have just the hotels in column A in sheet 2...so you just lock that column. you will allow sheet 2s value to move so that you can drag it all the way to the bottom of sheet 1...so no \$s. Same is true for B and C.

Good luck :D

Replies
1
Views
340
Replies
3
Views
476
Replies
0
Views
386
Replies
7
Views
314
Replies
8
Views
381

1,190,782
Messages
5,982,888
Members
439,804
Latest member
melD94

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