Lookup multiple criteria problem

[Stacy Rueda]

Hi guys,

i need help on what formula should I use on below data. I have two files that consists of hundred of rows, wherein I want to lookup the price from file 1 to file 2 that will meet the same destination.

Example: For Part number AQ-123 (it has many destinations from file 1), but in file 2 i want to lookup the price only the required destination. Hope someone can help me, or if there is the same thread, kindly reply with the link. Appreciate your response. Thank you in advance.

File 1
Part No.	Destination	Price
AQ-123	CN	$ 13.00
AQ-909	HK	$ 76.00
AQ-987	US	$ 23.00
AQ-671	US	$ 34.00
AQ-234	CN	$ 131.00
AQ-298	HK	$ 421.00
AQ-123	US	$ 872.00
AQ-123	HK	$ 909.00
AQ-123	JP	$ 16.00
File 2
Part No.	Destination	Price
AQ-298	HK
AQ-234	CN
AQ-123	CN
AQ-123	US
AQ-909	HK
AQ-987	US
AQ-671	US

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[jtakw]

Looks like we posted at the same time again, give a 10 minutes, I'll post an updated formula for you ( making dinner ).

 

[Stacy Rueda]

Yes sir, I just really want to lookup the price.

Yes, the duplicates is just the same. even first found only, it will don't care the second and the next rows, because each PN has unique price per destination.

 

[Fazza]

Yes, the duplicates is just the same. even first found only, it will don't care the second and the next rows, because each PN has unique price per destination.

With duplicates all the same, please change the SUMIFS to AVERAGEIFS

 

[jtakw]

Ok, updated formula, see how this works for you.

C19 INDEX/MATCH Array formula to be confirmed by CSE (Control, Shift, Enter), instructions below.
D19 AVERAGEIFS formula normally entered.

Don't forget to change/adjust cell references/range, and add sheet name, as needed, formula copied down.

Book1
	A	B	C	D
1	File 1
2
3	Part No.	Destination	Price
4	AQ-123	CN	$13.00
5	AQ-909	HK	$76.00
6	AQ-987	US	$23.00
7	AQ-671	US	$34.00
8	AQ-234	CN	$131.00
9	AQ-298	HK	$421.00
10	AQ-123	US	$872.00
11	AQ-123	HK	$909.00
12	AQ-123	US	$872.00
13	AQ-123	US	$872.00
14
15
16
17
18	Part No.	Destination	Price
19	AQ-298	HK	$421.00	$421.00
20	AQ-234	CN	$131.00	$131.00
21	AQ-123	CN	$13.00	$13.00
22	AQ-123	US	$872.00	$872.00
23	AQ-909	HK	$76.00	$76.00
24	AQ-987	US	$23.00	$23.00
25	AQ-671	US	$34.00	$34.00
Sheet586

Cell Formulas
Range	Formula
D19	=AVERAGEIFS(C$4:C$13,A$4:A$13,A19,B$4:B$13,B19)
C19	{=INDEX(C$4:C$13,MATCH(A19&"|"&B19,A$4:A$13&"|"&B$4:B$13,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

file1

Book1
	A	B	C
1	Part No.	Destination	Price
2	AQ-123	CN	$13.00
3	AQ-909	HK	$76.00
4	AQ-987	US	$23.00
5	AQ-671	US	$34.00
6	AQ-234	CN	$131.00
7	AQ-298	HK	$421.00
8	AQ-123	US	$872.00
9	AQ-123	HK	$909.00
10	AQ-123	JP	$16.00
Sheet1

file2

Book1
	A	B	C
1	Part No.	Destination	Price
2	AQ-298	HK	421
3	AQ-234	CN	131
4	AQ-123	CN	13
5	AQ-123	US	872
6	AQ-909	HK	76
7	AQ-987	US	23
8	AQ-671	US	34
Sheet1

In C2 of Sheet1 (in file2), control+shift+enter, and copy down:

=INDEX([file1.xlsx]Sheet1!$C$2:$C$10,MATCH($A2,IF([file1.xlsx]Sheet1!$B$2:$B$10=$B2,[file1.xlsx]Sheet1!$A$2:$A$10),0))

 

[Stacy Rueda]

Ok, updated formula, see how this works for you.

C19 INDEX/MATCH Array formula to be confirmed by CSE (Control, Shift, Enter), instructions below.
D19 AVERAGEIFS formula normally entered.

Don't forget to change/adjust cell references/range, and add sheet name, as needed, formula copied down.

	A	B	C	D
1	File 1
2
3	Part No.	Destination	Price
4	AQ-123	CN	$13.00
5	AQ-909	HK	$76.00
6	AQ-987	US	$23.00
7	AQ-671	US	$34.00
8	AQ-234	CN	$131.00
9	AQ-298	HK	$421.00
10	AQ-123	US	$872.00
11	AQ-123	HK	$909.00
12	AQ-123	US	$872.00
13	AQ-123	US	$872.00
14
15
16
17
18	Part No.	Destination	Price
19	AQ-298	HK	$421.00	$421.00
20	AQ-234	CN	$131.00	$131.00
21	AQ-123	CN	$13.00	$13.00
22	AQ-123	US	$872.00	$872.00
23	AQ-909	HK	$76.00	$76.00
24	AQ-987	US	$23.00	$23.00
25	AQ-671	US	$34.00	$34.00

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet586​

Worksheet Formulas Cell Formula D19 =AVERAGEIFS(C$4:C$13,A$4:A$13,A19,B$4:B$13,B19) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C19 {=INDEX(C$4:C$13,MATCH(A19&"|"&B19,A$4:A$13&"|"&B$4:B$13,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for your help, as of this time I'm still checking the above formula. Will post the result after.

 

[Stacy Rueda]

Hi @Aladin Akyurek thank you for the reply, I'm just curious with the formula. I'm wondering how it will run without the reference from file 2.

 

[Stacy Rueda]

With duplicates all the same, please change the SUMIFS to AVERAGEIFS

Thank you @Fazza, I didn't notice your reply, your idea sounds great. But I worry for the future if in case the price may change.

 

[Stacy Rueda]

Hi @jtakw, your formula really works. #N/A displays if no result found. I want to hide it using IFERROR, sorry I'm not good in excel formula.

 

[Aladin Akyurek]

Hi @Aladin Akyurek thank you for the reply, I'm just curious with the formula. I'm wondering how it will run without the reference from file 2.

How do you mean?