I have a table with age groups

|  nr |  age_from  |  age_to  |
|  1  |    35      |    37    |
|  2  |    36      |    40    |

and a the second table (second_table) with person_age and person_id.

|  person_age |  person_id  |
|  35         |  22334455   |
|  39         |  66778899   |
|  39         |  123456789  |
|  39         |  222456222  |

I need to find random 3 person_id's for the first table that fit this age group, for example:

|  nr |  age_from  |  age_to  | person_id1 | person_id2 |  Person_id3   
|  1  |    35      |    37    | 22334455 
|  2  |    36      |    40    | 123456789  | 66778899 (not the same as the first!!!)  | 222456222

Person_id must be must be unique, but VLOOKUP finds only the first matching row. It turns out 3 identical person_id's.

My excel function:

=VLOOKUP(RANDBETWEEN(A2:B2);second_table!A:B;2;FALSE)

But it returns identical persons, because finds the first.

CodePudding user response：

Is this what you are trying:

Formula in H1:

=LET(x,A2:INDEX(C:C,COUNTA(A:A)),z,IFERROR(DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(a,b,VSTACK(a,LET(y,FILTER(DROP(TOCOL(F:F,1),1)&"",(DROP(TOCOL(E:E,1),1)>=INDEX(x,b,2))*(DROP(TOCOL(E:E,1),1)<=INDEX(x,b,3)),""),TOROW(SORTBY(y,RANDARRAY(ROWS(y)))))))),1),""),HSTACK(VSTACK(A1:C1,x),VSTACK("person_id"&SEQUENCE(1,COLUMNS(z)),z)))

This will include more columns if need be:

Note: If a person's age falls in multiple categories his/her ID number will reflect in all of these categories too.

CodePudding user response：

• First result:

If your excel supports Filter, then you can use this formula. Instead of TOROW, you can also use Transpose function.

=TOROW(FILTER($F$2:$F$12,($E$2:$E$12>=I4)*($E$2:$E$12<=J4)))

• Second result:

=IFERROR(INDEX($F$2:$F$12,SMALL(IF(($E$2:$E$12>=$I12)*($E$2:$E$12<=$J12),ROW($F$2:$F$12)-ROW($F$1)),COLUMNS($K12:K12))),"")

Hope it clears..