VLOOKUP random distinct values

Question

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.

Answer 1

Here a solution assuming you can use TEXTSPLIT function. You can try the following formula in G2 cell:

=LET(ages, A2:A5, mapR, MAP(D2:D3, E2:E3, F2:F3, LAMBDA(nr, from,to, 
  LET(set, FILTER(B2:B5, (ages >= from) * (ages <= to),0), 
    n, IF(@set=0,0, ROWS(set)), m, MIN(n,nr), IF(n=0, "", 
    TEXTJOIN(",",,INDEX(set, SORTBY(SEQUENCE(m),RANDARRAY(m,,1,n,TRUE)))))))),
  IFERROR(1*TEXTSPLIT(TEXTJOIN(";",, mapR),",",";", TRUE),""))

Here is the output:

Explanation

We use MAP function to calculate the main result (mapR). On each row of mapR will be the ids values randomly selected concatenated by ,. FILTER function is used to match the search criteria Age from, Age To columns. We prevent the case no result was found, where set is the output of FILTER, adding the third input argument to FILTER (0, assuming no value from Person Id column has such value) and then to calculate the number of rows returned (n) via the following condition:

IF(@set=0,0, ROWS(set))

So when n is zero, an empty string is returned.

Then we need also to prevent the situation where the number of rows of set is lower than the numbers of rows required by nr on each iteration of MAP, via the following condition for m MIN(n,nr).

The condition to generate the random numbers is the following:

SORTBY(SEQUENCE(m),RANDARRAY(m,,1,n,TRUE))

it ensures no duplicated random numbers are generated. The output of previous result is used on INDEX function to select the rows from the FILTER result (set).

The last step generates the output array via TEXTSPLIT using the output of MAP function (mapR). We multiply the output of TEXTSPLIT by 1, to convert the result to a numeric value. Finally, IFERROR is used to replace #N/A with an empty string when the number of columns returned on a given rows is less than the maximum number of columns returned.

If the result can be returned in a string format, then you can replace the IFERROR call with:

TEXTSPLIT(TEXTJOIN(";",, mapR),",",";", TRUE,,"")

Using pad_with input argument from TEXTSPLIT function.

Answer 2

• 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..

Answer 3

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.