# Using INDEX MATCH with Vertical and Horizontal Criteria

#### Knyphe

##### New Member
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

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

#### Knyphe

##### New Member
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)``

##### MrExcel MVP
Sheet1 (data)

 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Plan Code COA Age Group Funding Base Rate Tax Rate Rate Range IGT Rate 2​ 1​ Child Under 19 Both \$50.00 ​ \$3.00 ​ \$4.00 ​ \$1.50 ​ 3​ 1​ Adult 19 & Over Both \$45.00 ​ \$3.25 ​ \$5.00 ​ \$1.25 ​ 4​ 1​ SPD All Ages Medical \$47.00 ​ \$2.39 ​ \$6.00 ​ \$1.10 ​ 5​ 2​ Child Under 19 Both \$48.00 ​ \$3.40 ​ \$5.50 ​ \$1.39 ​ 6​ 2​ Adult 19 & Over Both \$49.00 ​ \$5.10 ​ \$4.50 ​ \$1.40 ​ 7​ 2​ SPD All Ages Medical \$51.00 ​ \$4.30 ​ \$3.00 ​ \$1.50 ​ 8​ 3​ Child Under 19 Both \$53.00 ​ \$2.50 ​ \$3.70 ​ \$1.70 ​ 9​ 3​ Adult 19 & Over Both \$54.00 ​ \$3.80 ​ \$4.80 ​ \$1.30 ​ 10​ 3​ SPD All Ages Medical \$55.00 ​ \$1.80 ​ \$1.50 ​ \$1.90 ​

Sheet2 (processing)

 Row\Col A​ B​ C​ D​ E​ F​ 1​ Plan Code COA Age Group Funding Rate Category Rate 2​ 1​ Child Under 19 Both Base Rate 50​ 3​ 1​ Child Under 19 Both Tax Rate 3​ 4​ 1​ Child Under 19 Both Rate Range 4​ 5​ 1​ Child Under 19 Both IGT Rate 1.5​ 6​ 2​ Child Under 19 Both Base Rate 48​ 7​ 2​ Child Under 19 Both Tax Rate 3.4​ 8​ 2​ Child Under 19 Both Rate Range 5.5​ 9​ 2​ Child Under 19 Both IGT Rate 1.39​ 10​ 3​ Child Under 19 Both Base Rate 53​ 11​ 3​ Child Under 19 Both Tax Rate 2.5​ 12​ 3​ Child Under 19 Both Rate Range 3.7​ 13​ 3​ Child Under 19 Both IGT 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
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.

##### MrExcel MVP
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:

Replies
8
Views
438
Replies
1
Views
278
Replies
1
Views
378
Replies
0
Views
88
Replies
2
Views
282

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.

### Which adblocker are you using?

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

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