CODEWITHSUNDEEP
excelexcel-formula
Wauzl
Wauzl

Reputation: 971

Excel: Seeing, if a person is already in the list

Suppose I have a list of names and surnames and I want to have the possibility to test if a name/surname-combination is already in the list. I thought about using MATCH, but this can only do one value, I think.

Example:

  | A    | B       | C | D   | E    | F          |
1 | Name | Surname |   | Doe | John | is in list |
2 | Doe  | John    |   |     |      |            |
3 | Grey | Dorian  |   |     |      |            |
… | …    | …       |   |     |      |            |

Where A2:B100 is the data, D1:E1 is my input, and F1 should contain some function that outputs 'is in list' if the name/surname combination in D1:E1 is found somehwhere in my data A2:B100 and otherwise 'is not in list'. Any ideas on how to do it?

EDIT: My question is slightly different to the possible duplicate, because I am not asking where the match is, I'm just asking whether there is a match.

Upvotes: 0

Views: 1023

Answers (3)

Jerry
Jerry

Reputation: 71578

You could use COUNTIFS:

=COUNTIFS($A$1:$A$10, D1, $B$1:$B$10, E1)

[Returns a number greater than 0 for a match]

You'll probably have to use at least two of these, considering your surname and name can be interchanged (i.e. one where D is matched against Name, and E against surname like in the above, then a second one where D is matched against surname and E against name), and last wrap everything in an IF to get your displayed result.

Upvotes: 4

CallumDA
CallumDA

Reputation: 12113

This also works:

=SUMPRODUCT(--(A2:A100&B2:B100=D1&E1))

You could wrap it in an IF statement like this:

=IF(SUMPRODUCT(--(A2:A100&B2:B100=D1&E1)),"is in list","not in list")

More rigorous version (as per your comments)

=SUMPRODUCT((A2:A100=D1)*(B2:B100=E1))

Upvotes: 2

Nicolaesse
Nicolaesse

Reputation: 2724

Considering the following

+---+--------+-----------+---------------+
|   |   A    |     B     |       C       |
+---+--------+-----------+---------------+
| 1 |  Name  |  Surname  | Concatenation |
| 2 |  Doe   |  John     | DoeJohn       |
| 3 |  Grey  |  Dorian   | GreyDorian    |
+---+--------+-----------+---------------+

where cell C2 is like =TRIM(A2)&TRIM(B2)

Now you just have to use the following formula to catch if the name/surname is in the list (for example you can write it into the F2 cell):

=IF(ISERROR(VLOOKUP(TRIM(D2)&TRIM(E2),C:C,1,0)),"is NOT in the list","is in the list")

where in column D you have the name and in column E you have the surname of the person you are looking for.

Upvotes: 0

Related Questions