How can I use a formula to match multiple criteria from a different table in Excel?
jkupczak
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",""),"")
teylyn
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: How to combine Join with an aggregate function and group by
- Next: error ansible when use docker role
Related
Related Related
- 1
MongoDB - How can I use $elemMatch with multiple criteria
- 2
Excel - how can i use the Filter formula to account for the next month?
- 3
How can I use a single cell formula to display the name of a table?
- 4
How can I match values based on criteria?
- 5
How to use multiple criteria in a Select Case in Excel
- 6
How do I add a third criteria to this Excel formula?
- 7
Excel table inserting old formula in new rows. How can I reset to the new formula?
- 8
Index and Match Multiple Criteria in Excel
- 9
SUMIF with multiple match criteria in Excel
- 10
Excel: Multiple MATCH Criteria (with an IF thrown in)
- 11
How i can use a criteria in cicle for i
- 12
Excel formula to match unique ID values between tables and count based on multiple criteria
- 13
How can I query multiple key criteria?
- 14
How can I filter on multiple criteria simultaneously?
- 15
How can I filter an array on multiple criteria?
- 16
How can I select on multiple criteria
- 17
Excel: Use Cell in Formula Based On Criteria
- 18
How can I compare multiple rows with other rows from two different table and check if they are equal or not?
- 19
PyGTK - how can I use this table layout to match the background image?
- 20
Creating an SQL query with multiple counts and different criteria from one table
- 21
How can I create a table with different fields from different events?
- 22
How can I mix two formula in excel?
- 23
Lodash - How can I get all the object from a collection match an array of strings criteria
- 24
How can I use multiple conditionals and match to create a new variable?
- 25
How can I use XLOOKUP (or similar) to return multiple different return values from identical lookup values?
- 26
How can I copy data(include format, but except formula) from one excel to other excel?
- 27
I need a formula in my excel to check multiple criteria and give result in 3 ways
- 28
How to return the column heading for multiple matches, from multiple criteria, in Excel?
- 29
how can i use IF commands to show different information on a table?
Comments