Index, Match, Vlookup in a range - error for negative values

All of the negatives in columns F and G have min > max.

Remember that the larger the negative number is, the smaller the value it has.

Take the formula in C5 for example

=INDEX($H$2:$H$42,MATCH(1,IF($F$2:$F$42<=B5,IF($G$2:$G$42>=B5,1)),0))

There are no cells that meet this criteria
IF($F$2:$F$42<=B5
If B5 = -31, then
There are No values in F are less than or equal to B5.

Sorry I attached the wrong file, correct one attached. my formula based on two criteria isn't working :/

-999999 < -2.01, so there are no numbers between a min of -2.01 and a max of -999999.

Switch it to a min of -999999 and a max of -2.01. Similarly for all negative numbers.

Edit: Ninja'd

I used SUMPRODUCT instead of an array formula.

I don't get it.

Why does the formula have to look at column A at all? All of the 9 minimums, maximums, and status' just repeat for all 5 types.

Also, what should 0 days return? "Due Today" or "Backlog 0-14 days"?
Both have a min of 0 in your lookup table.

Attached is a workbook that has the lookup table in a cleaner format showing this.

Originally Posted by 63falcondude

I don't get it.

Why does the formula have to look at column A at all? All of the 9 minimums, maximums, and status' just repeat for all 5 types.

Also, what should 0 days return? "Due Today" or "Backlog 0-14 days"?
Both have a min of 0 in your lookup table.

Attached is a workbook that has the lookup table in a cleaner format showing this.

Hi,

Thanks for the reply, the column a is important be a use this data is an extract of a larger set.
The range can vary depending on the type.

Originally Posted by Tai1001

Hi,

Thanks for the reply, the column a is important be a use this data is an extract of a larger set.
The range can vary depending on the type.

If a solution is provided, please mark the thread as solved.

Hi All,

Thank you so much for your help!!

@SlipEternal
@63falcondude
@Jonmo1

you guys are great!

Sorry for the delay, I have been travelling!