Using INDEX to retrieve tabulated data

[dooners99]

Hi All,

A while back i got some assistance from a very helpful member with a formula to retrieve a value from the left most column, that was greater than Col "min" and less than col "MAX" from a range of values.

I never really worked out how this functioned and can’t really make it out of the help files on excel. I am now trying to do a similar lookup from a table of values but only need to find the row value using a greater than less than operation, using a single column.

The original formula was
{=INDEX(A:A,MIN(IF((INDIRECT("MAX")>B3)*(INDIRECT(“min")<B3),ROW(INDIRECT("min")),"")))}
Where B3 was the number to be compared against.

My main area that i get stuck on is why the formula muliplies the 2 indirect operations?

Can anyone shed some light on this ( and how i should modify this formula for my next challenge ) and possibly point me towards any Index / lookup related tutorials i may look into to get a better understanding of this function.

Thanks,

Dooners.

[DonkeyOte]

I think we would need to understand how the named ranges MIN & MAX are populated...

re: the multiplication... essentially the formula uses a MIN CSE array to return a ROW number... ie the lowest row number where the value < MAX and > MIN... the array of ROW numbers is generated by doing:

IF((MAX>value)*(MIN<value),ROW(Min))

Here a ROW value or default Boolean FALSE is returned to the array... the multiplication is used to generate a TRUE response in the IF ... in XL only the value 0 equates to FALSE, every other value be it 0.0000001 etc is equivalent to TRUE and we know an IF works like:

IF(test,do if true,do if false)

So in the above the "test" generates a numbers... given our "only 0 = FALSE" statement then we know if the value generated by the test is anything other than 0 the "do if true" will execute... to illustrate:

=IF(1,"Hello")
=IF(0,"Hello")

The first will return "Hello", the latter will return FALSE

How are you generating a number in the test ?
Well the multiplication coerces the Boolean results of each test to an Integer

(MAX>value)*(MIN<value)

ie both the above tests generate either TRUE/FALSE, if you multiply Booleans the result will be an integer, eg:

TRUE * TRUE = 1
TRUE * FALSE = 0
FALSE * TRUE = 0
FALSE * FALSE = 0

I hope that helps (?)

[dooners99]

Thanks DonkeyOte!

Ive got the principals and new fomula sorted.

Much appreciated!