Excel: Random select within table that meets two criteria

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

Here is an example of my data:

Name:        Office:   Years:

John Smith   Canada      1

Bob Smith    Canada      1

Jake Smith   Canada      1

Sarah Smith  Canada      1

On another sheet I want to output a random name based upon Office and Years. As I drag the formula down I want it to give a different name every time (this is where I'm stuck). I have tried several formulas and this is the closest. 

=INDEX($B$2:$B$436,MATCH(SMALL(IF($D$2:$D$436&$F$2:$F$436=$H$3&$I$3,$G$2:$G$436),1),$G$2:$G$436,0))

Column B = Name Column D=Office Column F=Years then H3 and I3 are my lookup values

column G is a set of random numbers = rand()

Best Answer

I'm going to put the randomization in the formula itself. You could always put this in another column if you want.

Column A is name
Column B is office
Column C is year

Cell G2 is the office you are interested in
Cell G3 is the year you are interested in

=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), RANDBETWEEN(1,COUNTIFS(B:B,G2,C:C,G3))))

This is an array formula and needs to be entered using Ctrl+Shift+Enter.

It works by creating an array that has the row numbers (if criteria is matched) or zero if it doesn't. Then it takes the nth largest value, where n is a random number between 1 and the number of rows that match the criteria.

Related Question