Randomly selecting items from list under certain conditions

**Excelleration** · 12-06-2009, 04:55 PM

Alright, this problem has been annoying me for the past 3 hours or so
I had to create a new account on here for help!

Basically I want a formula that will randomly select foods that are under a specific amount of calories and return the value as a randomly selected food which has an amount of calories that are under the specified amount

This is what I got so far:

INDEX(Recipes!E11:F20,MATCH(RANDBETWEEN(37,D18),Recipes!F11:F20,1),1)

Recipes!E11:F20 is the list of foods in E with the calories of them in F
Recipes!F11:F20 are the calories of the foods
D18 is the value that I want the formula to search for foods under

All this formula does is return around 4 foods, when its supposed to return more...

Please help!

**martindwilson** · 12-06-2009, 08:17 PM

MATCH(RANDBETWEEN(37,D18),Recipes!F11:F20,1),will find the largest value less than or = to look up and the values must be sorted ascending or youll get the sort of results you are experiencing.
try inserting another column say g and put =if(recipies!f11<$d$18,rand(),0)
then use
INDEX(Recipes!E11:e20,MATCH(max(Recipes!g11:g20),Recipes!g11:g20,0))