Using INDEX MATCH with Vertical and Horizontal Criteria

Knyphe

New Member
Joined
Feb 16, 2018
Messages
3
Hello Everyone,

I am stuck on figuring out how to make this work. I have a huge worksheet to populate from essentially a giant table with multiple criteria that needs to be checked. I am able to use Index Match to return a value from a column based on multiple criteria but am unable to make one to work for the table across columns. This example will hopefully explain it better, the real table has a lot of different plans and with about 20 columns based on rates. I am moving this data into a format essentially where each row is one of the rates with all of the information behind it. It is about 14k total rows. We are sending our data from an easily read format to their crappy format but we have to do it. I have also used SUMIFS to find the correct code. I essentially want to use a formula that will find match the first 4 rows and then match the column and return the value at that intersection. If not, I have to create 20 different formulas for each column and paste it 1k times. Thank you!


Plan Code
COA
Age Group
Funding
Base Rate
Tax Rate
Rate Range
IGT Rate
1
Child
Under 19
Both
$ 50.00
$ 3.00
$ 4.00
$ 1.50
1
Adult
19 & Over
Both
$ 45.00
$ 3.25
$ 5.00
$ 1.25
1
SPD
All Ages
Medical
$ 47.00
$ 2.39
$ 6.00
$ 1.10
2
Child
Under 19
Both
$ 48.00
$ 3.40
$ 5.50
$ 1.39
2
Adult
19 & Over
Both
$ 49.00
$ 5.10
$ 4.50
$ 1.40
2
SPD
All Ages
Medical
$ 51.00
$ 4.30
$ 3.00
$ 1.50
3
Child
Under 19
Both
$ 53.00
$ 2.50
$ 3.70
$ 1.70
3
Adult
19 & Over
Both
$ 54.00
$ 3.80
$ 4.80
$ 1.30
3
SPD
All Ages
Medical
$ 55.00
$ 1.80
$ 1.50
$ 1.90

<tbody>
</tbody>


Plan Code
COA
Age Group
Funding
Rate Category
Rate
1
Child
Under 19
Both
Base Rate
Formula Here
1
Child
Under 19
Both
Tax Rate

1
Child
Under 19
Both
Rate Range

1
Child
Under 19
Both
IGT Rate

2
Child
Under 19
Both
Base Rate

2
Child
Under 19
Both
Tax Rate

2
Child
Under 19
Both
Rate Range

2
Child
Under 19
Both
IGT Rate

3
Child
Under 19
Both
Base Rate

3
Child
Under 19
Both
Tax Rate

3
Child
Under 19
Both
Rate Range

3
Child
Under 19
Both
IGT Rate


<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Knyphe

New Member
Joined
Feb 16, 2018
Messages
3
Here is what I have tried so far.
Code:
{=INDEX('C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$BL$10:$BL$839,MATCH(1,(C695='C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$A$10:$A$839)*(D695='C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$G$10:$G$839),0))}

Code:
=SUMIFS('C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$BV$10:$BV$839,'C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$A$10:$A$839,$C629,'C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$G$10:$G$839,$D629,'C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$H$10:$H$839,$H629,'C:\Users\nleach\Desktop\[July17-Jun18 All Plans Classic ACA OE Increments TEST.xlsx]July17-Jun18 Increment'!$I$10:$I$839,$G629)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Sheet1 (data)

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
Plan CodeCOAAge GroupFundingBase RateTax RateRate RangeIGT Rate
2​
1
ChildUnder 19Both
$50.00
$3.00
$4.00
$1.50
3​
1
Adult19 & OverBoth
$45.00
$3.25
$5.00
$1.25
4​
1
SPDAll AgesMedical
$47.00
$2.39
$6.00
$1.10
5​
2
ChildUnder 19Both
$48.00
$3.40
$5.50
$1.39
6​
2
Adult19 & OverBoth
$49.00
$5.10
$4.50
$1.40
7​
2
SPDAll AgesMedical
$51.00
$4.30
$3.00
$1.50
8​
3
ChildUnder 19Both
$53.00
$2.50
$3.70
$1.70
9​
3
Adult19 & OverBoth
$54.00
$3.80
$4.80
$1.30
10​
3
SPDAll AgesMedical
$55.00
$1.80
$1.50
$1.90

Sheet2 (processing)

Row\Col
A​
B​
C​
D​
E​
F​
1​
Plan CodeCOAAge GroupFundingRate CategoryRate
2​
1
ChildUnder 19BothBase Rate
50
3​
1
ChildUnder 19BothTax Rate
3
4​
1
ChildUnder 19BothRate Range
4
5​
1
ChildUnder 19BothIGT Rate
1.5
6​
2
ChildUnder 19BothBase Rate
48
7​
2
ChildUnder 19BothTax Rate
3.4
8​
2
ChildUnder 19BothRate Range
5.5
9​
2
ChildUnder 19BothIGT Rate
1.39
10​
3
ChildUnder 19BothBase Rate
53
11​
3
ChildUnder 19BothTax Rate
2.5
12​
3
ChildUnder 19BothRate Range
3.7
13​
3
ChildUnder 19BothIGT Rate
1.7

In F2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=INDEX(Sheet1!$E$2:$H$10,MATCH(A2,IF(Sheet1!$B$2:$B$10=B2,IF(Sheet1!$C$2:$C$10=C2,
     IF(Sheet1!$D$2:$D$10=D2,Sheet1!$A$2:$A$10))),0),MATCH(E2,Sheet1!$E$1:$H$1,0))
 

Knyphe

New Member
Joined
Feb 16, 2018
Messages
3
Thank you so much! That worked! I was using * to connect the first arguments so maybe that screwed me up. Maybe it was all of the nesting and I had just screwed it up.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Thank you so much! That worked! I was using * to connect the first arguments so maybe that screwed me up. Maybe it was all of the nesting and I had just screwed it up.

You are welcome.

Rich (BB code):
=INDEX(Sheet1!$E$2:$H$10,MATCH(A2,IF(Sheet1!$B$2:$B$10=B2,IF(Sheet1!$C$2:$C$10=C2, IF(Sheet1!$D$2:$D$10=D2,Sheet1!$A$2:$A$10))),0),MATCH(E2,Sheet1!$E$1:$H$1,0))

can be re-written as:

Rich (BB code):
=INDEX(Sheet1!$E$2:$H$10,MATCH(1,IF(Sheet1!$B$2:$B$10=B2,IF(Sheet1!$C$2:$C$10=C2, IF(Sheet1!$D$2:$D$10=D2,IF(Sheet1!$A$2:$A$10=A2,1)))),0),MATCH(E2,Sheet1!$E$1:$H$1,0))

or as:
Rich (BB code):
=INDEX(Sheet1!$E$2:$H$10,MATCH(A2,IF((Sheet1!$B$2:$B$10=B2)*(Sheet1!$C$2:$C$10=C2)*(Sheet1!$D$2:$D$10=D2),Sheet1!$A$2:$A$10),0),
    MATCH(E2,Sheet1!$E$1:$H$1,0))
 
Last edited:

Forum statistics

Threads
1,137,349
Messages
5,680,965
Members
419,946
Latest member
Trickay

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
Top