Matching Multiple Cells in Two Tables Simultaneously

1. Matching Multiple Cells in Two Tables Simultaneously

QUESTION: How do you search two data tables simultaneously to find out the following:
Which customer + account + amount is found in what table?
Which customer + account + amount is found in both tables?
Which customer + account + amount is found in neither table?

IMPORTANT: All three values -- customer + account + amount -- must be matched.
The goal is to employ conditional formatting so that:
- The customer + account + amount set found in Table A is highlighted in BLUE.
- The customer + account + amount set found in Table B is highlighted in YELLOW.
- The customer + account + amount found in both tables is highlighted in GREEN.
- The customer + account + amount not found in either table is highlighted in RED.

ALSO: One table will likely contain more data than the other, ie: Table A may contain 150 records while Table B contains 200, etc.

ALTERNATIVELY
If conditional formatting is not workable, then maybe the result can identify the table(s) customer + account + amount set is found in, with the result cells looking something like this:
TABLE B
TABLE A
TABLE A & B

Thank you!

2. Re: Matching Multiple Cells in Two Tables Simultaneously

Try this.....

Select the range E3:G164 --> Conditional Formatting --> Make a New Rule for conditional formatting using the formulas given below and set format as per your choice.

For Data in both the Table A & Table B
``Please Login or Register  to view this content.``
For Data in Table A only.
``Please Login or Register  to view this content.``
For Data in Table B only.
``Please Login or Register  to view this content.``
For Data neither in Table A nor in Table B i.e. For Data Not Found.
``Please Login or Register  to view this content.``
For details see the attached sheet where I have applied the conditional formatting in the lookup table range.

Is this what you are trying to achieve?

3. Re: Matching Multiple Cells in Two Tables Simultaneously

Hello sktneer,

This is absolutely beautiful! Thank you!

Is there a formula I can also use in an adjacent column to include the text indications of which table (or lack thereof) the data was found? (Similar to what is shown on the spreadsheet I posted and the one you provided.)

Example:
TABLE A & B
TABLE A
TABLE B

Kind regards,

Dave Myers

4. Re: Matching Multiple Cells in Two Tables Simultaneously

You're welcome and thanks for the feedback.
For your new requirement, try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula.
``Please Login or Register  to view this content.``
and then copy down.

5. Re: Matching Multiple Cells in Two Tables Simultaneously

Hello sktneer,

Works flawlessly!

Thank you so much!

Kind regards,

Dave Myers

6. Re: Matching Multiple Cells in Two Tables Simultaneously

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)