How can I use a formula to match multiple criteria from a different table in Excel?

0
votes

I want to fill in several columns in a table based on data from another table. If an email exists in the original table, and its row also has a value that matches the Source column, return true.

This is what my original source table looks like.

Email            | Source
---------------------------
[email protected]   | A
[email protected]   | B
[email protected]      | A
[email protected]  | C
[email protected]      | A

And this is what my results table looks like.

Email            | A   | B   | C
-----------------------------------
[email protected]   | Yes | Yes |
[email protected]      | Yes |     |
[email protected]  |     |     | Yes
[email protected]      | Yes |     |

Columns with the headings A, B, and C would be the formulas checking for a true or false value based on the above table. The formula would then enter "Yes" for true or leave the cell blank for false.

Here is an example of what the formula would look like in Column A. I attempt to match Email to Email, and then the string "A" to the Source column. Unfortunately this returns true for all rows.

=IFERROR(IF(AND(MATCH([Email],source[Email],0),MATCH("A",source[Source],0)),"Yes",""),"")

2
excelexcel-formula

2 Answers

2
votes

What you describe can be achieved with a CountIFs function. Type the email addresses and the source types into the row and column headers of your result table, then use the formula

=IF(COUNTIFS($A:$A,$E2,$B:$B,F$1),"Yes","")

Adjust the ranges to suit your environment but take care with the relative and absolute references to ensure the formula looks at the correct ranges when copied to the right.

enter image description here

-1
votes

I would use a VLOOKUP. Not sure exactly how you need it look but it should be something like this.

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

=VLOOKUP([Email], source[Email], 2)
=VLOOKUP($A1, source[A:B], 2)

This will give you the value.

Now just compare that to the value at the top of the column. https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

=IF(VLOOKUP([Email], source[Email], 2)=B$1, "Yes", "")
=IF(VLOOKUP($A1, source[A:B], 2)=B$1, "Yes", "")