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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Australia
Country​
Weight​
Service​
Price​
2​
Weight​
Shipping Carrier # 1​
Shipping Carrier # 2​
Shipping Carrier # 3​
Shipping Carrier # 4​
Australia
1​
Shipping Carrier # 1
10​
3​
1​
10​
15​
20​
0​
Australia
1​
Shipping Carrier # 2
15​
4​
2​
10​
15​
20​
0​
Australia
1​
Shipping Carrier # 3
20​
5​
3​
0​
0​
0​
0​
Australia
2​
Shipping Carrier # 1
10​
6​
4​
0​
0​
0​
0​
Australia
2​
Shipping Carrier # 2
15​
7​
Australia
2​
Shipping Carrier # 3
20​
8​
Belgium
1​
Shipping Carrier # 1
10​
9​
Belgium
1​
Shipping Carrier # 2
15​
10​
Belgium
1​
Shipping Carrier # 3
20​
11​
Belgium
2​
Shipping Carrier # 1
10​
12​
Belgium
2​
Shipping Carrier # 2
15​
13​
Belgium
2​
Shipping Carrier # 3
20​

<tbody>
</tbody>
 
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...

Row\Col
A​
B​
C​
D​
1​
Australia
2​
WeightShipping Carrier # 1Shipping Carrier # 2Shipping Carrier # 3
3​
1​
10​
15​
20​
4​
2​
10​
15​
20​
5​
3​
10​
15​
30​
6​
4​
10​
15​
35​
7​

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

Do the same for other countries.

Row\Col
A​
B​
C​
D​
1​
CountryWeightServicePrice
2​
Australia
1​
Shipping Carrier # 1
10​
3​
Australia
1​
Shipping Carrier # 2
15​
4​
Australia
1​
Shipping Carrier # 3
20​
5​
Australia
2​
Shipping Carrier # 1
10​
6​
Australia
2​
Shipping Carrier # 2
15​
7​
Australia
2​
Shipping Carrier # 3
20​
8​
Belgium
1​
Shipping Carrier # 1
9​
Belgium
1​
Shipping Carrier # 2
10​
Belgium
1​
Shipping Carrier # 3
11​
Belgium
2​
Shipping Carrier # 1
12​
Belgium
2​
Shipping Carrier # 2
13​
Belgium
2​
Shipping Carrier # 3

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

F
  • Question
Multiple OR criteria in argument
Replies
2
Views
170
awoohaw
awoohaw
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
245
mst3kr
M
P
  • Question
Dynamic Spilled Array Formula using byrow
Replies
8
Views
233
pto160
P
T
  • Question
Lookup within a lookup
2
Replies
10
Views
292
TimeForTim
T
willow1985
  • Solved
Index match formula with multiple matches
2
Replies
14
Views
800
Alex Blakenburg
A

Forum statistics

Threads
1,215,459
Messages
6,124,947
Members
449,198
Latest member
MhammadishaqKhan

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