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.

[TABLE="width: 289"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]File 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part No.[/TD]
[TD]Destination[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]CN[/TD]
[TD] $ 13.00[/TD]
[/TR]
[TR]
[TD]AQ-909[/TD]
[TD]HK[/TD]
[TD] $ 76.00[/TD]
[/TR]
[TR]
[TD]AQ-987[/TD]
[TD]US[/TD]
[TD] $ 23.00[/TD]
[/TR]
[TR]
[TD]AQ-671[/TD]
[TD]US[/TD]
[TD] $ 34.00[/TD]
[/TR]
[TR]
[TD]AQ-234[/TD]
[TD]CN[/TD]
[TD] $ 131.00[/TD]
[/TR]
[TR]
[TD]AQ-298[/TD]
[TD]HK[/TD]
[TD] $ 421.00[/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]US[/TD]
[TD] $ 872.00[/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]HK[/TD]
[TD] $ 909.00[/TD]
[/TR]
[TR]
[TD]AQ-123 [/TD]
[TD]JP[/TD]
[TD] $ 16.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part No.[/TD]
[TD]Destination[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]AQ-298[/TD]
[TD]HK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-234[/TD]
[TD]CN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]CN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]US[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-909[/TD]
[TD]HK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-987[/TD]
[TD]US[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ-671[/TD]
[TD]US[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

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.

With IFERROR to show "" Blank:

Book1
	A	B	C
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
20	AQ-234	CN	$131.00
21	AQ-123	CN	$13.00
22	AQ-123	US	$872.00
23	AQ-909	HK	$76.00
24	AQ-987	US	$23.00
25	AQ-671	US	$34.00
26	AB-123	US
Sheet586

Cell Formulas
Range	Formula
C19	{=IFERROR(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.

 

[Stacy Rueda]

Hi @jtakw, THANK YOU SO MUCH

 

[jtakw]

You're very welcome, thanks for the feedback.