-1

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!

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
dev 1245
  • 11
  • 1
  • 3

2 Answers2

2

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))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This works perfect! Thank you Ron. I'm sorry for all the confusion, it's my first time asking for help in this forum. Also, do you happen to know if there is a specific formula or a way that I could get this formula to ignore Uppercase and lower case? – dev 1245 Oct 29 '18 at 13:14
  • @BrayanRodriguez That wasn't stated in your question. I will post an edit for case insensitive. – Ron Rosenfeld Oct 29 '18 at 13:18
  • It works flawlessly. I will be more thorough next time. Thank you again. – dev 1245 Oct 29 '18 at 13:25
1

try,

=INDEX(E:E, aggregate(15, 7, row(e:e)/((c$1:index(c:c, match("zzz", c:c))=a2)*(d$1:index(d:d, match("zzz", c:c))=b2)), 1))