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.

	A	B	C	D	E
1	Limit	$10000	$15000	$20000	$25000
2	Zone 1	$10	$20	$30	n/a
3	Zone 2	$10	n/a	n/a	n/a

<tbody>
</tbody>

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