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,

This is one way, change/adjust cell references/range, add sheet name, as needed:

Book1
	A	B	C
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	JP	$16.00
13
14	File 2
15
16	Part No.	Destination	Price
17	AQ-298	HK	$421.00
18	AQ-234	CN	$131.00
19	AQ-123	CN	$13.00
20	AQ-123	US	$872.00
21	AQ-909	HK	$76.00
22	AQ-987	US	$23.00
23	AQ-671	US	$34.00
Sheet583

Cell Formulas
Range	Formula
C17	=SUMIFS(C$4:C$12,A$4:A$12,A17,B$4:B$12,B17)

Formula copied down.

 

[Stacy Rueda]

Hi @jtakw, Thank you very much for your reply. I tried your formula using the data above and it works, but during the actual file, the result is 0 in all rows. Is there an effect because I have different worksheet?

 

[jtakw]

Yes, as I mentioned above,

change/adjust cell references/range, add sheet name, as needed

You need to add the sheet name to the formula where it references where your lookup table resides, sample:

=SUMIFS(SheetName!C$4:C$12,SheetName!A$4:A$12,A17,SheetName!B$4:B$12,B17)

 

[Stacy Rueda]

Hi @jtakw, thank you again for the quick reply. I changed the sheet name before, I just overlooked the A17 cell. Thank you very much.

 

[Stacy Rueda]

Hi again @jtakw, I'm sorry but how it was happened that the formula above doesn't sum the price of PN? I'm sorry I thought it was ok already because what happened to my file is all price for AQ-123 sums up all the destination. Thank you in advance for the reply

 

[Stacy Rueda]

Hi @jtakw, My file should not need to sum up the price, just looking at the price per destination. Is it possible If can use Index - Match

 

[jtakw]

I'm not understanding your problem, can you explain what the issue is?
Are None of the Part No. Not summing?, Or some do, and some don't?

Describe what Is calculating correctly and what is Not.
Also, where does the data come from ( Part No., Destination, Price ), is it imported from another source, manually entered, or?

 

[Stacy Rueda]

Hi @jtakw, Sorry, but as per checking in my file, there are AQ-123 that has US repeatedly three times, then it doesn't need to sum all the three destination. I just need to get the price of US destination. Then per destination is fixed price, it doesn't change. Sorry I revised my first table. Please check AQ-123, the result should be $872 and not 2616 (sum). Thank you in advance.


For example

[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[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]US

[/TD]
[TD]$ 872.00
[/TD]
[/TR]
[TR]
[TD]AQ-123[/TD]
[TD]US[/TD]
[TD]$872.00



[/TD]
[/TR]
[TR]
[TD][/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]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi @jtakw, My file should not need to sum up the price, just looking at the price per destination. Is it possible If can use Index - Match

Looks like we posted at the same time.

Ok, so it seems you may have "duplicate" matching Part No. and Destinations, and you just want to lookup the Price, is that correct?

If so, would the duplicates be the same?, cause if we do an INDEX/MATCH, it'll return the First found only, or if they may be different, then what should happen?