Excel Formula across worksheets: If a=x, b=y then c=z

Question

I started off using

=IF(AND(A1="this",B1="that"),"x","")

=IF(AND('[Employee Emails.xlsx]Alpha Order'!$A$2=A2,'[Employee Emails.xlsx]Alpha Order'!$B$2=B2, ???? > THEN I want it to pull the next cell '[Employee Emails.xlsx]Alpha Order'!$C$2 into C2. So C2 would display the email address.

Hopefully if firstname & lastname match, then it pulls the email address into the new cell. Otherwise I have to manually look them up & copy & paste into the new workbook.

So you want to search a list of names and where A2 and B2 match in that list return the value in a third column in that same row? — Scott Craner, May 18 '18 at 14:56
look here: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another — Scott Craner, May 18 '18 at 14:57

score 0 · Answer 1 · answered May 18 '18 at 14:57

0

I think this is what you want:

=IF(AND('[Employee Emails.xlsx]Alpha Order'!$A$2=A2,'[Employee Emails.xlsx]Alpha Order'!$B$2=B2), '[Employee Emails.xlsx]Alpha Order'!$C$2,"")

Though you may also want to look into VLOOKUP and INDEX/MATCH

answered May 18 '18 at 14:57

cybernetic.nomad

6,100
3
18
31

score 0 · Answer 2 · answered May 18 '18 at 15:23

If this were your data:

    A   B   C  D E  F G
1   A   G   1    A  G
2   B   H   2    C  I
3   C   I   3    D  K
4   D   K   4           
5   E   L   5           
6   F   M   6

In G1 you'd need this array formula (applied with Ctrl+Shift+Enter):

=INDEX($D$1:$D$6,MATCH(F1&G1,$B$1:$B$6&$C$1:$C$6,0))

And it would produce this:

    A   B   C  D E  F G
1   A   G   1    A  G 1
2   B   H   2    C  I 3
3   C   I   3    D  K 4
4   D   K   4           
5   E   L   5           
6   F   M   6

Hopefully you'll find this helpful.

Excel Formula across worksheets: If a=x, b=y then c=z

2 Answers2