Filtering data on multiple sheet by comparing columns

Filtering data on multiple sheet by comparing columns

Question:

I have spent hours making a database with multiple sheets. one with properties i have available and one with applicants looking for properties. I understand the basics of conditional formatting and if statements but i am now struggling. is there a formula that will match alert me if there are any matches between the two sheets. for example.

Sheet1 (Screenshot):

Sheet2 (Screenshot):

I would like the sheet to match, column B with B, C with C and D with D. so if the budget, price and move dates match it tells me in a separate cell or sheet would be even better.


Solution:

If you want to get alerted even if only Column B of Sheet1 matches to Column B of Sheet2 then put the following formulas in the mentioned below Cells on Sheet 3:
at Cell A1 on sheet3: =ARRAYFORMULA(IF(Sheet1!A1:A<>"";IF(Sheet1!A1:A=Sheet2!A1:A;"MATCHES";"");""))
at Cell A2 on sheet3: =ARRAYFORMULA(IF(Sheet1!B1:B<>"";IF(Sheet1!B1:B=Sheet2!B1:B;"MATCHES";"");""))
at Cell A3 on sheet3: =ARRAYFORMULA(IF(Sheet1!C1:C<>"";IF(Sheet1!C1:C=Sheet2!C1:C;"MATCHES";"");""))
at Cell A4 on sheet3: =ARRAYFORMULA(IF(Sheet1!D1:D<>"";IF(Sheet1!D1:D=Sheet2!D1:D;"MATCHES";"");""))

And if you want directly when all three conditions match then you can directly insert the following  formula:
at A5 on sheet3: =ARRAYFORMULA(IF(Sheet1!D1:D<>"";IF(Sheet1!B1:B=Sheet2!B1:B;IF(Sheet1!C1:C=Sheet2!C1:C;IF(Sheet1!D1:D=Sheet2!D1:D;"MATCHES";"");"");"");""))

You can see the results of above two sheets (sheet1 and sheet2) in the following screenshot of Sheet3, Cell A2 and Cell A4 are matching, Similarly Cell B3 and B4 are matching.

Sheet3 (Screenshot):

Look at the following Screenshot of the Sheet4, if you want only the filtered results where:
Column B of Sheet1 matches Column B of Sheet2
Column C of Sheet1 matches Column C of Sheet2
Column D of Sheet1 matches Column D of Sheet2

I have inserted the following formula in Cell A1 of Sheet4:
=FILTER(Sheet1!A1:D;Sheet1!B1:B=Sheet2!B1:B;Sheet1!C1:C=Sheet2!C1:C;Sheet1!D1:D=Sheet2!D1:D)

Sheet4 (Screenshot):

I hope this will help you out.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive