Modifying a current index match and making it more dynamic.

kcgojnur

Board Regular
Joined
Aug 13, 2014
Messages
122
I currently have the following formula:

Code:
<code>=INDEX('All Other Subsidy'!F10:I32,MATCH('Rate Testing'!C58,'All Other Subsidy'!A10:A32,0),MATCH(input_tier_medical,'All Other Subsidy'!F9:I9,0))</code>

What it is doing is based on a zip code input pulling a subsidy rate associated. It currently works as expected and needs to remain functional. However, I need to do is add a bit of complexity to it. I'm hoping someone can help.

If C4 equals "EE_Only" and C8 equals X15:X22 than I should get "404.67". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Spouse" and C8 equals X15:X22 than I should get "832.41". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Child(ren)" and C8 equals X15:X22 than I should get "624.81". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Family" and C8 equals X15:X22 than I should get "1139.54". If both of these criteria are not meet the referenced code should run.

Any help is greatly appreciated.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
thin k you need to give more info on this?

Assuming that X15:X22 is referring to a range, how can a cell contain a range?
What exactly is in C8?
 
Upvote 0
I currently have the following formula:

Code:
<code>=INDEX('All Other Subsidy'!F10:I32,MATCH('Rate Testing'!C58,'All Other Subsidy'!A10:A32,0),MATCH(input_tier_medical,'All Other Subsidy'!F9:I9,0))</code>

What it is doing is based on a zip code input pulling a subsidy rate associated. It currently works as expected and needs to remain functional. However, I need to do is add a bit of complexity to it. I'm hoping someone can help.

If C4 equals "EE_Only" and C8 equals X15:X22 than I should get "404.67". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Spouse" and C8 equals X15:X22 than I should get "832.41". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Child(ren)" and C8 equals X15:X22 than I should get "624.81". If both of these criteria are not meet the referenced code should run.
If C4 equals "EE+Family" and C8 equals X15:X22 than I should get "1139.54". If both of these criteria are not meet the referenced code should run.

Any help is greatly appreciated.

Cell C8 is a field that is used for the user to enter their zip-code

X15:X22 - 93620, 93635, 95301, 95340, 95726, 95945, 95949, 95959, 95975
 
Last edited:
Upvote 0
I put your EE-etc in it's own table...
T​
U​
15​
EE_Only404.67
16​
EE+Spouse832.41
17​
EE+Child(ren)624.81
18​
EE+Family1139.54

Then used this...
=IF(ISERROR(MATCH(C8,$X$15:$X$23,0)),"your-formula",VLOOKUP(C4,$T$15:$U$18,2,0))
 
Upvote 0
I put your EE-etc in it's own table...
T​
U​
15​
EE_Only404.67
16​
EE+Spouse832.41
17​
EE+Child(ren)624.81
18​
EE+Family1139.54

<tbody>
</tbody>


Then used this...
=IF(ISERROR(MATCH(C8,$X$15:$X$23,0)),"your-formula",VLOOKUP(C4,$T$15:$U$18,2,0))

Thanks FD, I'll give it a try. I think I may have stumbled across something.

Code:
<code>=IFERROR(CHOOSE(MATCH(C4,{"EE_Only","EE+Child(ren)","EE+Spouse","EE+Family"},0),404.67,624.81,832.41,1139.54)/(COUNTIF(X15:X22,C8)>0),INDEX('All Other Subsidy'!F10:I32,MATCH('Rate Testing'!C58,'All Other Subsidy'!A10:A32,0),MATCH(input_tier_medical,'All Other Subsidy'!F9:I9,0)))</code>
 
Upvote 0
Thanks FD, I'll give it a try. I think I may have stumbled across something.

Code:
<code>=IFERROR(CHOOSE(MATCH(C4,{"EE_Only","EE+Child(ren)","EE+Spouse","EE+Family"},0),404.67,624.81,832.41,1139.54)/(COUNTIF(X15:X22,C8)>0),INDEX('All Other Subsidy'!F10:I32,MATCH('Rate Testing'!C58,'All Other Subsidy'!A10:A32,0),MATCH(input_tier_medical,'All Other Subsidy'!F9:I9,0)))</code>

That should be:

=IFERROR(CHOOSE(MATCH($C4,{"EE_Only","EE+Child(ren)","EE+Spouse","EE+Family"},0),404.67,624.81,832.41,1139.54)/(COUNTIF($X$15:$X$22,$C8)>0),INDEX('All Other Subsidy'!$F$10:$I$32,MATCH('Rate Testing'!$C58,'All Other Subsidy'!$A$10:$A$32,0),MATCH(input_tier_medical,'All Other Subsidy'!$F$9:$I$9,0)))

where input_tier_medical must be a defined name, pointing to a cell (otherwise, it should be double-quoted).

Another option:

=IF(ISNUMBER(MATCH($C8,$X$15:$X$22,0)),VLOOKUP($C4,{"EE_Only",404.67;"EE+Child(ren)",624.81;"EE+Spouse",832.41;"EE+Family",1139.54},2,0),INDEX('All Other Subsidy'!$F$10:$I$32,MATCH('Rate Testing'!$C58,'All Other Subsidy'!$A$10:$A$32,0),MATCH(input_tier_medical,'All Other Subsidy'!$F$9:$I$9,0)))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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