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