Help with IndexCollect

System

This discussion was created from comments split from: Can you use 2 match criterias in an index/match formula?.

USAFRetired20

I am looking to do something similar to this.

Goal: If "Availability" column is .9832 AND the consecutive years is 3, then penalty is $400,000.

I have used this formula that works if "availability" matches the index exactly, but not between:

=INDEX(COLLECT(Penalty1:Penalty90, CONSECUTIVE YEARS1:CONSECUTIVE YEARS90, [Consecutive Yrs]@row, IndexB1:IndexB90, Availability@row), 1)

Index Consecutive Years Penalty

0.9999	0	$0.00
0.9999	2	$0.00
0.9999	3	$0.00
0.9999	4	$0.00
0.9999	5	$0.00
0.9999	6	$0.00
0.9999	7	$0.00
0.9999	8	$0.00
0.98288	0	$100,000.00
0.98288	2	$250,000.00
0.98288	3	$400,000.00
0.98288	4	$550,000.00

prime_nathaniel

@USAFRetired20 can you possibly rewrite this request, i think when they split it off to its own topic something may have gotten lost in translation. The way it is written

"Goal: If "Availability" column is .9832 AND the consecutive years is 3, then penalty is $400,000."

Does not appear to have anything to do with "but not between:"

And this formula example does not match the data references provided

Index | Consecutive Years | Penalty

=INDEX(COLLECT(Penalty1:Penalty90, CONSECUTIVE YEARS1:CONSECUTIVE YEARS90, [Consecutive Yrs]@row, IndexB1:IndexB90, Availability@row), 1)

Is index and availability the same thing? or maybe misnamed?

Paul Newcome

Try this:

=INDEX(COLLECT(Penalty1:Penalty90, CONSECUTIVE YEARS1:CONSECUTIVE YEARS90, [Consecutive Yrs]@row, IndexB1:IndexB90, @cell <= Availability@row), 1)