INDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

O

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to use IDEX/MATCH lookup from an Excel sheet like the one below:
Code: 
[table]
[tr]
	[th]Country[/th]
	[th]Weight[/th]
	[th]Service[/th]
	[th]Price[/th]
[/tr]
[tr]
	[td]Australia[/td]
	[td]1[/td]
	[td]Shipping Carrier # 1[/td]
	[td]10[/td]
[/tr]
[tr]
	[td]Australia[/td]
	[td]1[/td]
	[td]Shipping Carrier # 2[/td]
	[td]15[/td]
[/tr]
[tr]
	[td]Australia[/td]
	[td]1[/td]
	[td]Shipping Carrier # 3[/td]
	[td]20[/td]
[/tr]
[tr]
	[td]Australia[/td]
	[td]2[/td]
	[td]Shipping Carrier # 1[/td]
	[td]10[/td]
[/tr]
[tr]
	[td]Australia[/td]
	[td]2[/td]
	[td]Shipping Carrier # 2[/td]
	[td]15[/td]
[/tr]
[tr]
	[td]Australia[/td]
	[td]2[/td]
	[td]Shipping Carrier # 3[/td]
	[td]20[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]1[/td]
	[td]Shipping Carrier # 1[/td]
	[td]10[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]1[/td]
	[td]Shipping Carrier # 2[/td]
	[td]15[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]1[/td]
	[td]Shipping Carrier # 3[/td]
	[td]20[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]2[/td]
	[td]Shipping Carrier # 1[/td]
	[td]10[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]2[/td]
	[td]Shipping Carrier # 2[/td]
	[td]15[/td]
[/tr]
[tr]
	[td]Belgium[/td]
	[td]2[/td]
	[td]Shipping Carrier # 3[/td]
	[td]20[/td]
[/tr]
[/table]
I want to be able to get the price amount by searching the country, service, and weight; so I will have the weight numbers listed down different rows and the shipping carriers going across the top with the country name on one cell like A1=Australia
Code: 
Australia 

[table]
[tr]
	[th]Weight[/th]
	[th]Shipping Carrier # 1[/th]
	[th]Shipping Carrier # 2[/th]
[/tr]
[tr]
	[td]1[/td]
	[td]10.00[/td]
	[td]10.00[/td]
[/tr]
[tr]
	[td]2[/td]
	[td]10.00[/td]
	[td]10.00[/td]
[/tr]
[tr]
	[td]3[/td]
	[td]10.00[/td]
	[td]10.00[/td]
[/tr]
[tr]
	[td]4[/td]
	[td]10.00[/td]
	[td]10.00[/td]
[/tr]
[/table]

Does anyone know how I would be able to do this with INDEX/MATCH lookup with searching these 3 criteria?

Thank you!
 
Sort by date Sort by votes
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

maybe something like....

=SUMIFS(Price,Country,$A$1,Service,B$2,Weight,$A3)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Australia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Country​
[/TD]
[TD]
Weight​
[/TD]
[TD]
Service​
[/TD]
[TD]
Price​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
Weight​
[/TD]
[TD]
Shipping Carrier # 1​
[/TD]
[TD]
Shipping Carrier # 2​
[/TD]
[TD]
Shipping Carrier # 3​
[/TD]
[TD]
Shipping Carrier # 4​
[/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 1[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
1​
[/TD]
[TD]
10​
[/TD]
[TD]
15​
[/TD]
[TD]
20​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 2[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
2​
[/TD]
[TD]
10​
[/TD]
[TD]
15​
[/TD]
[TD]
20​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 3[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
3​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 1[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
4​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 2[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Australia[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 3[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 1[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 2[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
1​
[/TD]
[TD]Shipping Carrier # 3[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 1[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 2[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Belgium[/TD]
[TD]
2​
[/TD]
[TD]Shipping Carrier # 3[/TD]
[TD]
20​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

It's unclear how Belgium is to be found. Is this country in a sheet of its own?
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

@Weazel Yes, that worked! :) Thank you... I thought I had to use INDEX/MATCH for this but the SUMIFS worked out great and a lot easier to setup.

@Aladin Akyurek Yes, my plan was to set up each country on it's own sheet.

Thank you!
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

olimits7 said:
@Weazel Yes, that worked! :) Thank you... I thought I had to use INDEX/MATCH for this but the SUMIFS worked out great and a lot easier to setup.

@Aladin Akyurek Yes, my plan was to set up each country on it's own sheet.

Thank you!
Click to expand...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]Australia[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
2​
[/td][td]Weight[/td][td]Shipping Carrier # 1[/td][td]Shipping Carrier # 2[/td][td]Shipping Carrier # 3[/td][/tr]


[tr][td]
3​
[/td][td]
1​
[/td][td]
10​
[/td][td]
15​
[/td][td]
20​
[/td][/tr]


[tr][td]
4​
[/td][td]
2​
[/td][td]
10​
[/td][td]
15​
[/td][td]
20​
[/td][/tr]


[tr][td]
5​
[/td][td]
3​
[/td][td]
10​
[/td][td]
15​
[/td][td]
30​
[/td][/tr]


[tr][td]
6​
[/td][td]
4​
[/td][td]
10​
[/td][td]
15​
[/td][td]
35​
[/td][/tr]


[tr][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Name A2:D6 Australia (the sheet name might be the same.)

Do the same for other countries.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]Country[/td][td]Weight[/td][td]Service[/td][td]Price[/td][/tr]


[tr][td]
2​
[/td][td]Australia[/td][td]
1​
[/td][td]Shipping Carrier # 1[/td][td]
10​
[/td][/tr]


[tr][td]
3​
[/td][td]Australia[/td][td]
1​
[/td][td]Shipping Carrier # 2[/td][td]
15​
[/td][/tr]


[tr][td]
4​
[/td][td]Australia[/td][td]
1​
[/td][td]Shipping Carrier # 3[/td][td]
20​
[/td][/tr]


[tr][td]
5​
[/td][td]Australia[/td][td]
2​
[/td][td]Shipping Carrier # 1[/td][td]
10​
[/td][/tr]


[tr][td]
6​
[/td][td]Australia[/td][td]
2​
[/td][td]Shipping Carrier # 2[/td][td]
15​
[/td][/tr]


[tr][td]
7​
[/td][td]Australia[/td][td]
2​
[/td][td]Shipping Carrier # 3[/td][td]
20​
[/td][/tr]


[tr][td]
8​
[/td][td]Belgium[/td][td]
1​
[/td][td]Shipping Carrier # 1[/td][td][/td][/tr]


[tr][td]
9​
[/td][td]Belgium[/td][td]
1​
[/td][td]Shipping Carrier # 2[/td][td][/td][/tr]


[tr][td]
10​
[/td][td]Belgium[/td][td]
1​
[/td][td]Shipping Carrier # 3[/td][td][/td][/tr]


[tr][td]
11​
[/td][td]Belgium[/td][td]
2​
[/td][td]Shipping Carrier # 1[/td][td][/td][/tr]


[tr][td]
12​
[/td][td]Belgium[/td][td]
2​
[/td][td]Shipping Carrier # 2[/td][td][/td][/tr]


[tr][td]
13​
[/td][td]Belgium[/td][td]
2​
[/td][td]Shipping Carrier # 3[/td][td][/td][/tr]
[/table]

In D2 of this look up sheet enter and copy down:

=IFERROR(VLOOKUP($B2,INDIRECT($A2),MATCH($C2,INDEX(INDIRECT($A2),1,0),0),0),"")

Note that I had not implemented the Belgium table, hence no values for those entries.
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

Hi,

@Aladin Akyurek Ok, thank you...I will give this a try too!

I have another issue now from a different table; on this table I think INDEX/MATCH will be a better fit but can't seem to get it to work.

I want to find the "Price" based on the following table where in 1 cell I will list the "Zone" letter (e.g. A) and in the other cell I will list the "Weight" (e.g. 2).

Code: 
A1 = A (Zone)
A2 = 2 (Weight)

[table]
[tr]
	[th]Weight[/th]
	[th]A[/th]
	[th]B[/th]
	[th]C[/th]
	[th]D[/th]
[/tr]
[tr]
	[td]1[/td]
	[td]15.00[/td]
	[td]15.00[/td]
	[td]15.00[/td]
	[td]15.00[/td]
[/tr]
[tr]
	[td]2[/td]
	[td]20.00[/td]
	[td]20.00[/td]
	[td]20.00[/td]
	[td]20.00[/td]
[/tr]
[tr]
	[td]3[/td]
	[td]25.00[/td]
	[td]25.00[/td]
	[td]25.00[/td]
	[td]25.00[/td]
[/tr]
[tr]
	[td]4[/td]
	[td]30.00[/td]
	[td]30.00[/td]
	[td]30.00[/td]
	[td]30.00[/td]
[/tr]
[tr]
	[td]5[/td]
	[td]35.00[/td]
	[td]35.00[/td]
	[td]35.00[/td]
	[td]35.00[/td]
[/tr]
[tr]
	[td]6[/td]
	[td]40.00[/td]
	[td]40.00[/td]
	[td]40.00[/td]
	[td]40.00[/td]
[/tr]
[/table]

What would be the best way to return the "Price" amount based on what the "Zone" and "Weight" is that I have entered in these 2 cells?

Thank you, again!
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

Select the table area, including the first row with Weight, etc. and name the selection ztable.

Now invoke:

=vlookup(a2,ztable,match(a1,index(ztable,1,0),0),0)
 
Upvote 0
Re: IDEX/MATCH Lookup - Multiple Criteria (3) Formula Based On Table In Thread?

Yes, that worked...thank you! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
SumIFs formula multiple criteria (Visible Cells)
Replies
5
Views
351
felixstraube
F
I
  • Question Question
Divide cell value so weekly stock is balance between warehouses
Replies
1
Views
343
Kaper
Kaper
F
  • Question Question
Multiple OR criteria in argument
Replies
2
Views
337
awoohaw
awoohaw
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
211
flashgordie
F
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
1K
mst3kr
M

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