Search code examples
excelexcel-formulaarray-formulas

Excel 2016 - If four cells match, return value of 5th cell

For example,

Column A: First Name

Column B: Last Name

Column C: First Name

Column D: Last Name

Column E: ID

I'm looking for a function that will return the value of E in a new column if A matches with C, and B matches with D. Would have to match the names even if they are out of order. For example, A+B might be in row 5 and match with C+D in row 7, then return E

I was able to make a function that matches only the first names, but get stuck trying to add the last names into the equation. 

=INDEX(E:E,MATCH(A2,C:C,0))

example

Thank you in advance!

Solution

  • Try this:

    =INDEX($E:$E,MATCH(1,EXACT(A2,$C:$C)*EXACT(B2,$D:$D),0))

    The above is an array formula. To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

    enter image description here

    Notes:

    • The formula will return #N/A if there is no match.
    • The formula will be more efficient if you can shorten the range references to be only a portion of the column
    • For a case-insensitive version, we remove the EXACT function:
    =INDEX($E:$E,MATCH(1,(A2=$C:$C)*(B2=$D:$D),0))