# Excel: VLOOKUP + randomized data depending on the cell

#### excelnewbieneedhelp

##### New Member
Hello guys

Thanks for viewing my thread, I have some question regarding on using VLOOKUP+randomized data.

Sheet1

<code>branchID

BRANCH01
BRANCH02
BRANCH03
BRANCH04
</code> Sheet2

<code>employeeID

EMPLOYEE01 -- BRANCH01
EMPLOYEE02 -- BRANCH01
EMPLOYEE03 -- BRANCH02
EMPLOYEE04 -- BRANCH02
EMPLOYEE05 -- BRANCH03
EMPLOYEE06 -- BRANCH03
EMPLOYEE07 -- BRANCH04
EMPLOYEE08 -- BRANCH04

What I want for the result -
Sheet3

BRANCHID EMPLOYEEID

</code><code><code>BRANCH01 </code></code><code><code><code>EMPLOYEE01 </code>
<code><code><code><code><code>BRANCH01 EMPLOYEE02
BRANCH03 EMPLOYEE05
BRANCH03 EMPLOYEE06
BRANCH04 EMPLOYEE07
BRANCH01 EMPLOYEE02
BRANCH01 EMPLOYEE01
BRANCH02 EMPLOYEE03

which that the employeeID is depending on branchid (and it will randomized the value between(EMPLOYEE01 & EMPLOYEE02) whenthe branchid is BRANCH01, sames goes to BRANCH02 (EMPLOYEE03 &EMPLOYEE04) , BRANCH03 (EMPLOYEE05 & EMPLOYEE06) , BRANCH04(EMPLOYEE07 & EMPLOYEE08)</code></code></code></code></code></code><code><code><code><code><code>
</code></code></code></code></code>

</code>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### StephenCrump

##### Well-known Member
Welcome to the Forum!

Perhaps something like this:

E3: =INDEX(\$B\$2:\$B\$10,AGGREGATE(15,6,(ROW(B\$1:B\$10)-ROW(B\$1)+1)/(A\$2:A\$10=D3),RANDBETWEEN(1,COUNTIF(A\$2:A\$10,D3))))

But as always, it depends what you mean by "random". Perhaps in this case you want each of the employees 11, 33, 44, 77 and 99 to appear once each (i.e. not randomly), but in random order?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Branch ID</td><td style="font-weight: bold;text-align: center;;">Employee ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">A</td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Branch</td><td style="font-weight: bold;text-align: center;;">Random</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">B</td><td style="text-align: center;;">22</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">A</td><td style="text-align: center;;">33</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">B</td><td style="text-align: center;;">55</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">77</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">B</td><td style="text-align: center;;">66</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">A</td><td style="text-align: center;;">77</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">B</td><td style="text-align: center;;">88</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">A</td><td style="text-align: center;;">99</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />

Last edited:

#### excelnewbieneedhelp

##### New Member
Welcome to the Forum!

Perhaps something like this:

E3: =INDEX(\$B\$2:\$B\$10,AGGREGATE(15,6,(ROW(B\$1:B\$10)-ROW(B\$1)+1)/(A\$2:A\$10=D3),RANDBETWEEN(1,COUNTIF(A\$2:A\$10,D3))))

But as always, it depends what you mean by "random". Perhaps in this case you want each of the employees 11, 33, 44, 77 and 99 to appear once each (i.e. not randomly), but in random order?

ABCDE
1Branch IDEmployee ID
2A11BranchRandom
3B22A11
4A33A44
5A44A44
6B55A77
7B66A44
8A77
9B88
10A99

</tbody>

Hello, Thanks for the response :D

sorry I don't think that is what I wanted.

i will explain more with pics ( sorry for poor english , not my main languages )

http://prntscr.com/j22ogn ( BRANCH sheet )

http://prntscr.com/j22oq5 ( Employee sheet)

http://prntscr.com/j22ow3 (Sheet that i need to combine both data (employee and branch) )

in the third picture, I used 'RANDBETWEEN' to random around 400 data between (BRANCH01 to BRANCH04), and I wanted to do the same to my employeeID too , but there is a requirement which that the EMPLOYEEID need to match with the BRANCHID

#### excelnewbieneedhelp

##### New Member
Need help on this

#### StephenCrump

##### Well-known Member
The solution will depend on exactly what you mean by "random"?

Expanding on my previous example:

D3: =INDEX(A\$15:A\$17,RANDBETWEEN(1,COUNTA(A\$15:A\$17)))
randomly selects branch A, B or C from the unique branch list. Note that using this formula A, B and C are equally likely to be selected. Is this OK? Or should we perhaps select Branch A three times as frequently as B or C, given that A has six employees, and B and C each have two employees?

E3: =INDEX(\$B\$2:\$B\$11,AGGREGATE(15,6,(ROW(B\$1:B\$11)-ROW(B\$1)+1)/(A\$2:A\$11=D3),RANDBETWEEN(1,COUNTIF(A\$2:A\$11,D3))))
randomly selects an employee from the randomly selected Branch B. Note that for Branch C, employee 88 has been randomly selected four times, and employee 66 nil times. Is this OK? Or do you want "random" to mean something different?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">Branch ID</td><td style="font-weight: bold;text-align: center;color: #333333;;">Employee ID</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td><td style="text-align: right;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;">Branch</td><td style="font-weight: bold;text-align: center;color: #333333;;">Random</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">33</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">44</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">66</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">77</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">99</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">00</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;color: #333333;;">Unique Branch ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />

Last edited:

Replies
4
Views
361
Legacy 68668
L