Modifying a current index match and making it more dynamic.

[kcgojnur]

I currently have the following formula:

<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.

 

[FDibbins]

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?

 

[kcgojnur]

I currently have the following formula:

<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

 

[kcgojnur]

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?

Hi FD, I have provided the criteria for X15:X22 and C8

 

[FDibbins]

I put your EE-etc in it's own table...
[Table="width:, class:grid"][tr][td] [/td][td]

T​

[/td][td]

U​

[/td][/tr]
[tr][td]

15​

[/td][td]EE_Only[/td][td]404.67[/td][/tr]

[tr][td]

16​

[/td][td]EE+Spouse[/td][td]832.41[/td][/tr]

[tr][td]

17​

[/td][td]EE+Child(ren)[/td][td]624.81[/td][/tr]

[tr][td]

18​

[/td][td]EE+Family[/td][td]1139.54[/td][/tr]
[/table]

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

 

[kcgojnur]

I put your EE-etc in it's own table...
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

T​

[/TD]
[TD]

U​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]EE_Only[/TD]
[TD]404.67[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]EE+Spouse[/TD]
[TD]832.41[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]EE+Child(ren)[/TD]
[TD]624.81[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]EE+Family[/TD]
[TD]1139.54[/TD]
[/TR]
</tbody>[/TABLE]


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>=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>

 

[Aladin Akyurek]

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

<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)))