Using INDEX MATCH MATCH to find next "possible" value in array

[evanseabrook]

I have a table that contains Limits($), Zones and the resulting charge amount.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Limit[/TD]
[TD]$10000[/TD]
[TD]$15000[/TD]
[TD]$20000[/TD]
[TD]$25000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Zone 1[/TD]
[TD]$10[/TD]
[TD]$20[/TD]
[TD]$30[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Zone 2[/TD]
[TD]$10[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
</tbody>[/TABLE]

I have the above table. I want to pass in a zone number and a limit amount. If the resulting charge is "n/a", I want to return the charge amount for that zone that isn't "n/a" along with the associated Limit value.

I'm able to return the charge amount for a passed in zone number and limit amount with no problems. I've been playing around with an INDEX MATCH MATCH formula but I'm spinning my wheels.

In the below formula:
T1 contains the limit amount
V1 contains the zone number.
Z1 is the charge amount that is returned when I pass in the zone number and limit amount.

The below formula is for a second column to handle the n/a in sort of a what if scenario.

=INDEX('externaltable'!$B$2:$E$3,MATCH(V1,'externaltable!'$A$2:$A$3),IF(Z1="n/a",MAX('externaltable'!$B$1:$E$1),MATCH(T1,'externaltable'!$B$17:$M$17)))

 

[Aladin Akyurek]

Try to provide the input values of T1, V1, and Z1 along with the expected value.

 

[evanseabrook]

Try to provide the input values of T1, V1, and Z1 along with the expected value.

T1 = 20000
V1 = Zone 2
Z1 = n/a

Passing these values in, I expect to get back $10 and 10000 respectively.

I guess a better explanation is I'm trying to use 2 formulas. One to return the highest charge for the particular zone and the other to return the associated limit.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(IF($B$1:$E$1<=T1,IF(ISNUMBER(INDEX($B$2:$E$3,MATCH(V1,$A$2:$A$3,0),0)),INDEX($B$2:$E$3,MATCH(V1,$A$2:$A$3,0),0))))

 

[evanseabrook]

Control+shift+enter, not just enter:

=MAX(IF($B$1:$E$1<=T1,IF(ISNUMBER(INDEX($B$2:$E$3,MATCH(V1,$A$2:$A$3,0),0)),INDEX($B$2:$E$3,MATCH(V1,$A$2:$A$3,0),0))))

Works like a charm for the charm! Thanks! I think I'm following along with what's going on, but I also want to return the corresponding limit? Is it similar?

 

[Aladin Akyurek]

Works like a charm for the charm! Thanks! I think I'm following along with what's going on, but I also want to return the corresponding limit? Is it similar?

I forgat to include that. Yes it's similar:

Control+shift+enter:

=MAX(IF($B$1:$E$1<=T1,IF(ISNUMBER(INDEX($B$2:$E$3,MATCH(V1,$A$2:$A$3,0),0)),$B$1:$E$1)))