Using INDEX MATCH with Vertical and Horizontal Criteria

K

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!


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


[TABLE="width: 0"]
<tbody>[TR]
[TD]Plan Code
[/TD]
[TD]COA
[/TD]
[TD]Age Group
[/TD]
[TD]Funding
[/TD]
[TD]Rate Category
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Base Rate
[/TD]
[TD] Formula Here
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Tax Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Rate Range
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]IGT Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Base Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Tax Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Rate Range
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]IGT Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Base Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Tax Rate
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]Rate Range
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Child
[/TD]
[TD]Under 19
[/TD]
[TD]Both
[/TD]
[TD]IGT Rate
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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)
 
Upvote 0
Sheet1 (data)

[Table="width:, class:grid"][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][/tr][tr][td]
1​
[/td][td]Plan Code[/td][td]COA[/td][td]Age Group[/td][td]Funding[/td][td]Base Rate[/td][td]Tax Rate[/td][td]Rate Range[/td][td]IGT Rate[/td][/tr]
[tr][td]
2​
[/td][td]
1​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]
$50.00 ​
[/td][td]
$3.00 ​
[/td][td]
$4.00 ​
[/td][td]
$1.50 ​
[/td][/tr]
[tr][td]
3​
[/td][td]
1​
[/td][td]Adult[/td][td]19 & Over[/td][td]Both[/td][td]
$45.00 ​
[/td][td]
$3.25 ​
[/td][td]
$5.00 ​
[/td][td]
$1.25 ​
[/td][/tr]
[tr][td]
4​
[/td][td]
1​
[/td][td]SPD[/td][td]All Ages[/td][td]Medical[/td][td]
$47.00 ​
[/td][td]
$2.39 ​
[/td][td]
$6.00 ​
[/td][td]
$1.10 ​
[/td][/tr]
[tr][td]
5​
[/td][td]
2​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]
$48.00 ​
[/td][td]
$3.40 ​
[/td][td]
$5.50 ​
[/td][td]
$1.39 ​
[/td][/tr]
[tr][td]
6​
[/td][td]
2​
[/td][td]Adult[/td][td]19 & Over[/td][td]Both[/td][td]
$49.00 ​
[/td][td]
$5.10 ​
[/td][td]
$4.50 ​
[/td][td]
$1.40 ​
[/td][/tr]
[tr][td]
7​
[/td][td]
2​
[/td][td]SPD[/td][td]All Ages[/td][td]Medical[/td][td]
$51.00 ​
[/td][td]
$4.30 ​
[/td][td]
$3.00 ​
[/td][td]
$1.50 ​
[/td][/tr]
[tr][td]
8​
[/td][td]
3​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]
$53.00 ​
[/td][td]
$2.50 ​
[/td][td]
$3.70 ​
[/td][td]
$1.70 ​
[/td][/tr]
[tr][td]
9​
[/td][td]
3​
[/td][td]Adult[/td][td]19 & Over[/td][td]Both[/td][td]
$54.00 ​
[/td][td]
$3.80 ​
[/td][td]
$4.80 ​
[/td][td]
$1.30 ​
[/td][/tr]
[tr][td]
10​
[/td][td]
3​
[/td][td]SPD[/td][td]All Ages[/td][td]Medical[/td][td]
$55.00 ​
[/td][td]
$1.80 ​
[/td][td]
$1.50 ​
[/td][td]
$1.90 ​
[/td][/tr]
[/table]

Sheet2 (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td]Plan Code[/td][td]COA[/td][td]Age Group[/td][td]Funding[/td][td]Rate Category[/td][td]Rate[/td][/tr]
[tr][td]
2​
[/td][td]
1​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Base Rate[/td][td]
50​
[/td][/tr]
[tr][td]
3​
[/td][td]
1​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Tax Rate[/td][td]
3​
[/td][/tr]
[tr][td]
4​
[/td][td]
1​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Rate Range[/td][td]
4​
[/td][/tr]
[tr][td]
5​
[/td][td]
1​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]IGT Rate[/td][td]
1.5​
[/td][/tr]
[tr][td]
6​
[/td][td]
2​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Base Rate[/td][td]
48​
[/td][/tr]
[tr][td]
7​
[/td][td]
2​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Tax Rate[/td][td]
3.4​
[/td][/tr]
[tr][td]
8​
[/td][td]
2​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Rate Range[/td][td]
5.5​
[/td][/tr]
[tr][td]
9​
[/td][td]
2​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]IGT Rate[/td][td]
1.39​
[/td][/tr]
[tr][td]
10​
[/td][td]
3​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Base Rate[/td][td]
53​
[/td][/tr]
[tr][td]
11​
[/td][td]
3​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Tax Rate[/td][td]
2.5​
[/td][/tr]
[tr][td]
12​
[/td][td]
3​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]Rate Range[/td][td]
3.7​
[/td][/tr]
[tr][td]
13​
[/td][td]
3​
[/td][td]Child[/td][td]Under 19[/td][td]Both[/td][td]IGT Rate[/td][td]
1.7​
[/td][/tr]
[/table]

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))
 
Upvote 0
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.
 
Upvote 0
Knyphe said:
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.
Click to expand...

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:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
INDEX MATCH only returning first match
Replies
5
Views
676
Stuepef
S
M
  • Solved
Index and Match
Replies
3
Views
55
Peter_SSs
Peter_SSs
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
91
Fluff
Fluff
D
  • Solved
Index/match multiple criteria not working - HELP please
Replies
5
Views
326
NickFru
N
k3yn0t3
  • Solved
INDIRECT with INDEX(MATCH())?
2
Replies
10
Views
1K
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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