random from list

[paulk]

hi there

i been trying to come up with a function to return a random from list in column A if is between the numbers in column B & C.

A B C
Rhodes 1 3
Dee Why 5 7
Inmark 2 10
Blacktown 44 66
Zetland 1 5

For example a number 2 returns random from rhodes, inmark, zetland.

I manged to work it out using some helper columns, but im trying to do it in a single function.

Thanks for you help.

 

[mikerickson]

If I undestand the criteria:
Given 2, "Rhodes" is in the pot because 1 <= 2 < 3
"Dee Why" is not in the pot because NOT(5 <= 2 < 7)
etc.

Then choose a random string from the pot.
Is that the desired result?

(If so, does "High Equal" 1 2 go in the pot?)

You mention a solution using helper columns. Could you post that solution?
It should be possible to combine them into a single formula.

 

[paulk]

Thanks for quick replay

If I undestand the criteria:
Given 2, "Rhodes" is in the pot because 1 <= 2 < 3
"Dee Why" is not in the pot because NOT(5 <= 2 < 7)
etc.

Then choose a random string from the pot.
Is that the desired result?

correct. Column B <= 2 < Column C for each row.

In the helper column i had this formula =IF(AND(2>=B1,2<=C1),A1,"") to find the pot, than found a random from those results.

 

[mikerickson]

This CSE formula will return a random value from the "filtered" column A.

=INDEX(A:A, SMALL(IF(($B$1:$B$5<=2)*(2 < $C$1:$C$5)=1,ROW($A$1:$A$5)), RANDBETWEEN(1,SUMPRODUCT(--($B$1:$B$5<=2)*(2 < $C$1:$C$5)))), 1)

or, if you used named ranges and put 2 in E1:

=INDEX(A:A, SMALL(IF((LowArray<=$E$1)*($E$1 < HighArray)=1, ROW(NameArray)), RANDBETWEEN(1,SUMPRODUCT(--(LowArray<=$E$1)*($E$1 < HighArray)))), 1)

These formulas have to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[paulk]

works great, just what i was looking for.

Thank you very much.