Vlookup with If statement or matching index with If statement

[Bwalsh]

I’m trying to do something that I thought would be relatively simple but turns out to be more complicated than I thought and I am stuck. I have two sheets with data and need to do the following:

Look up an email address located in sheet one (column E), find the email in the second sheet (column D) and then if the customer number in the first sheet (column f) equals the customer number in the second sheet (column E) I then need it to return the information in Column F (order filled) of the second sheet.

I tried to do a vlookup with the if function and tried index matching but neither returned any values.


I’m working with Excel 2016. Any help or suggestions would be most appreciated. Thank you!

 

[philwojo]

I'm sure there are better ways to do this, but try this out:

=IF(INDEX(Sheet2!$D:$F,MATCH(Sheet1!$E1,Sheet2!$D:$D,0),2)=$F1,INDEX(Sheet2!$D:$F,MATCH(Sheet1!$E1,Sheet2!$D:$D,0),3),"Customer Nums Don't Match")

 

[philwojo]

I will add that what I provided will not take in to account duplicate emails, so it will only find the 1st possible match based on emails.

I also think this version might work a little better as it will also call out if it can't find an email:

=IF(ISERROR(INDEX(Sheet2!$D:$F,MATCH(Sheet1!$E1,Sheet2!$D:$D,0),2)=$F1),"Email match not found",IF(INDEX(Sheet2!$D:$F,MATCH(Sheet1!$E1,Sheet2!$D:$D,0),2)=$F1,INDEX(Sheet2!$D:$F,MATCH(Sheet1!$E1,Sheet2!$D:$D,0),3),"Customer Nums Don't Match"))

 

[AhoyNC]

Since you have Excel 2016 here is another option:
However this will only return the first value it finds for the Email / Cust#.

As pointed out by philwojo could you have a customer listed more than once where you need to bring back multiple answers from column F on sheet2?

Excel Workbook
	E	F	G
1	email	Cust#	Order
2	person1@yahoo	1	no match
3	person2@yahoo	2	1705
4	person4@yahoo	5	1548
5	person3@gmail	4	1265
6	person1@gmail	3	1473
Sheet1

Excel Workbook
	D	E	F
1	email	Cust#	Order
2	person1@yahoo	2	1192
3	person2@yahoo	2	1705
4	person1@gmail	3	1473
5	person3@gmail	4	1265
6	person4@yahoo	5	1548
Sheet2

 

[Bwalsh]

Since you have Excel 2016 here is another option:
However this will only return the first value it finds for the Email / Cust#.

As pointed out by philwojo could you have a customer listed more than once where you need to bring back multiple answers from column F on sheet2?

Sheet1

	E	F	G
person1@yahoo
person2@yahoo
person4@yahoo
person3@gmail
person1@gmail

<colgroup><col style="width:30px; "><col style="width:137px;"><col style="width:64px;"><col style="width:80px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Cust#[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Order[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]no match[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1705[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1548[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1265[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1473[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =IFERROR(INDEX(Sheet2!$F$2:$F$6,AGGREGATE(15,6,(ROW(Sheet2!$F$2:$F$6)-ROW(Sheet2!$F$2)+1)/(($E2=Sheet2!$D$2:$D$6)*($F2=Sheet2!$E$2:$E$6)),1)),"no match") <tbody> </tbody>

<tbody>
</tbody>

Sheet2

	D	E	F
person1@yahoo
person2@yahoo
person1@gmail
person3@gmail
person4@yahoo

<colgroup><col style="width:30px; "><col style="width:135px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Cust#[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Order[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1192[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1705[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1473[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1265[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1548[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

There can be more than one email associated with a customer number but that is why I want to return the value only if the customer number on the first sheet matches on customer number on the second sheet.

 

[Bwalsh]

There can be more than one email associated with a customer number but that is why I want to return the value only if the customer number on the first sheet matches on customer number on the second sheet. That should eliminate any duplicates and only return one value. I guess I am not as well versed in this as I thought as I am having trouble adding your formula with my actual data to return a value other than "Customer Nums Don't Match" Any chance you wouldn't mind clarifying what number should be added after the column numbers? I'm not sure which ones should be searching the whole document and which ones should be returning the actual value. I apologize for my lack of understanding.

Sheet 1 – titled “WandM” (first line of data starts at row 5)
Sheet 2 – titled “Report” (first line of data stars at row 2). Total table is A1:L25423


I am adding the formula to G2 (WandM) and am looking to return the value from column F (report)

 

[philwojo]

Have you tried updating the formula from Post #4 , that should take in to account duplicate emails, with your sheet names? Essentially you would replace anywhere in that formula that says SHEET2 with your actual sheet name, which would be Report.

Then his formula assumes you have a header in row-1 like in his example, if you do then leave the starting numbers as F2, D2, etc., just update the ending numbers from his 6 to your 25423.

The only part I am not sure of is right after the "Aggregate" portion of the formula, I don't know if any of that needs to change or not, I have not used that before, so maybe AhoyNC can chime in, or someone else, but at least you can try it and see if it works for you and post back in the mean time. If it doesn't work I would also post back your updated formula.

 

[AhoyNC]

See if this works:
I'm a little confused since you state that the data starts in row 5 of WandM, but you are putting the formula in row 2. Wouldn't the formula need to be entered in row 5 of WandM and then copied down?

Excel Workbook
	E	F	G
4	email	Cust#	Order
5	person2@yahoo	2	1705
6	person1@yahoo	1	no match
7	person4@yahoo	5	1548
8	person3@gmail	4	1265
9	person1@gmail	3	1473
WandM

Excel Workbook
	D	E	F
1	email	Cust#	Order
2	person1@yahoo	2	1192
3	person2@yahoo	2	1705
4	person1@gmail	3	1473
5	person3@gmail	4	1265
6	person4@yahoo	5	1548
Report

 

[Bwalsh]

See if this works:
I'm a little confused since you state that the data starts in row 5 of WandM, but you are putting the formula in row 2. Wouldn't the formula need to be entered in row 5 of WandM and then copied down?

WandM

	E	F	G
person2@yahoo
person1@yahoo
person4@yahoo
person3@gmail
person1@gmail

<colgroup><col style="width:30px; "><col style="width:137px;"><col style="width:64px;"><col style="width:80px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Cust#[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Order[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1705[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]no match[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1548[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1265[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1473[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G5 =IFERROR(INDEX(Report!$F$2:$F$25423,AGGREGATE(15,6,(ROW(Report!$F$2:$F$25423)-ROW(Report!$F$2)+1)/(($E5=Report!$D$2:$D$25423)*($F5=Report!$E$2:$E$25423)),1)),"no match") <tbody> </tbody>

<tbody>
</tbody>

Report

	D	E	F
person1@yahoo
person2@yahoo
person1@gmail
person3@gmail
person4@yahoo

<colgroup><col style="width:30px; "><col style="width:135px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Cust#[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Order[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1192[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1705[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1473[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1265[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1548[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Apologies, you are correct. I am trying to insert the formula in G5. I can't get the formula you supplied to return anything either, I think at this point I am just going to do it manually. Thanks so much for your help.

 

[AhoyNC]

One thig to check if you are actual getting a match. Could the Cust# be a number in one worksheet and text in the other?

Try the Match test formula below. If it returns #NA it didn't find a match, if it returns a number that would be the row it found the match on. If it returns all #NA 's then there is something different in either (or both) the email addresses or the Cust# between the two sheets.

Excel Workbook
	E	F	G	H
4	email	Cust#	Order	Match Check
5	person2@yahoo	2	1705	2
6	person1@yahoo	1	no match	#N/A
7	person4@yahoo	5	1548	5
8	person3@gmail	4	1265	4
9	person1@gmail	3	1473	3
WandM