Hi all, using Excel 2016, I'm trying to create a formula that will allow a user to select multiple criteria from some columns, and it will return the email address if there is a match in any of the columns. So far I have the following code working in that it returns the correct email addresses, but is returning the email address twice if it matches both criteria:
I'm not able to upload a sample file due to personal info and restrictions, but I've created a table below to represent the data
Now, the formula is entered into Z7 as an array and dragged down 100 cells. It references Values within cells Z2 and Z3 that it searches for. So in this example, assume Z2=Seattle, and Z3=Miami.
Right now the formula returns the following:
bobbarker@email.com
stevemadden@email.com
stevemadden@email.com
fredflintstone@email.com
This is technically correct in that jimcarrey@email.com is skipped as it doesn't match any, but it reports stevemadden@email.com twice as it matches both criteria.
How do I update it so that if the email already exists on the list, it is skipped/only listed once? I'd like to try and save users the need to have to remove duplicates manually. Thank you.
=IFERROR(INDEX($B$5:$B$100, AGGREGATE(15, 6, (ROW($S$5:$Y$100)-ROW($S$5)+1)/((ISNUMBER(SEARCH($Z$2, $S$5:$Y$100))) + (ISNUMBER(SEARCH($Z$3, $S$5:$Y$100)) * (COUNTIF($Z$6:Z6, INDEX($B$5:$B$100, AGGREGATE(15, 6, (ROW($S$5:$Y$100)-ROW($S$5)+1)/ISNUMBER(SEARCH($Z$2, $S$5:$Y$100)), ROW(1:1))))=0))), ROW(1:1))), "")I'm not able to upload a sample file due to personal info and restrictions, but I've created a table below to represent the data
| Email (B4) | Columns C-R | Site 1 (S4) | Site 2 (T4) | Site 3 (U4) | Site 4 (V4) | Site 5 (X4) | Site 6 (Y4) |
| bobbarker@email.com | New York | Seattle | Philadelphia | Austin | |||
| stevemadden@email.com | Miami | Chicago | Seattle | Philadelphia | Anchorage | Austin | |
| jimcarrey@email.com | New York | Houston | Chicago | ||||
| fredflintstone@email.com | Miami | New York | Houston | Dallas |
Now, the formula is entered into Z7 as an array and dragged down 100 cells. It references Values within cells Z2 and Z3 that it searches for. So in this example, assume Z2=Seattle, and Z3=Miami.
Right now the formula returns the following:
bobbarker@email.com
stevemadden@email.com
stevemadden@email.com
fredflintstone@email.com
This is technically correct in that jimcarrey@email.com is skipped as it doesn't match any, but it reports stevemadden@email.com twice as it matches both criteria.
How do I update it so that if the email already exists on the list, it is skipped/only listed once? I'd like to try and save users the need to have to remove duplicates manually. Thank you.
