Lookup multiple criteria problem

S

Stacy Rueda

Board Regular
Joined
Jun 23, 2016
Messages
87
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]
 
Sort by date Sort by votes
Looks like we posted at the same time again, give a 10 minutes, I'll post an updated formula for you ( making dinner ;) ).
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
Stacy Rueda said:
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.
Click to expand...
With duplicates all the same, please change the SUMIFS to AVERAGEIFS
 
Upvote 0
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
ABCD
1File 1
2
3Part No.DestinationPrice
4AQ-123CN$13.00
5AQ-909HK$76.00
6AQ-987US$23.00
7AQ-671US$34.00
8AQ-234CN$131.00
9AQ-298HK$421.00
10AQ-123US$872.00
11AQ-123HK$909.00
12AQ-123US$872.00
13AQ-123US$872.00
14
15
16
17
18Part No.DestinationPrice
19AQ-298HK$421.00$421.00
20AQ-234CN$131.00$131.00
21AQ-123CN$13.00$13.00
22AQ-123US$872.00$872.00
23AQ-909HK$76.00$76.00
24AQ-987US$23.00$23.00
25AQ-671US$34.00$34.00
Sheet586
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
file1


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


file2

Book1
ABC
1Part No.DestinationPrice
2AQ-298HK421
3AQ-234CN131
4AQ-123CN13
5AQ-123US872
6AQ-909HK76
7AQ-987US23
8AQ-671US34
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))
 
Upvote 0
jtakw said:
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.

ABCD
File 1
Part No.DestinationPrice
AQ-123CN
AQ-909HK
AQ-987US
AQ-671US
AQ-234CN
AQ-298HK
AQ-123US
AQ-123HK
AQ-123US
AQ-123US
Part No.DestinationPrice
AQ-298HK
AQ-234CN
AQ-123CN
AQ-123US
AQ-909HK
AQ-987US
AQ-671US

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"][/TD]

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

[TD="align: right"]$13.00 [/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]$76.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$23.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$34.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$131.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$421.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]$872.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]$909.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]$872.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$872.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]$421.00[/TD]
[TD="align: right"]$421.00[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]$131.00[/TD]
[TD="align: right"]$131.00[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]$13.00[/TD]
[TD="align: right"]$13.00[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]$872.00[/TD]
[TD="align: right"]$872.00[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]$76.00[/TD]
[TD="align: right"]$76.00[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]$23.00[/TD]
[TD="align: right"]$23.00[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]$34.00[/TD]
[TD="align: right"]$34.00[/TD]

</tbody>
Sheet586​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]=AVERAGEIFS(C$4:C$13,A$4:A$13,A19,B$4:B$13,B19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]{=INDEX(C$4:C$13,MATCH(A19&"|"&B19,A$4:A$13&"|"&B$4:B$13,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Thank you for your help, as of this time I'm still checking the above formula. Will post the result after. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
873
onlyadrafter
O

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top