Is there a function to randomly lookup certain values with the same ID?-CodePudding

E.g

Name | Product

John | A

Mary | B

Cary | C

John | D

Mary | E

Is there a way to do a random Vlookup()? For example if I were to lookup for John, either A or D will be shown.

CodePudding user response：

You can use XLOOKUP() to search values either first to last or last to first.

=XLOOKUP(E1,A2:A6,B2:B6,,0,1)

Last parameter define search direction. To search last to first use -1.

To return all matching data you can use FILTER() function like-

=FILTER(A2:B6,A2:A6=E1)

Edit: If you really need to show random result then use below formula.

=INDEX(FILTER(B2:B6,A2:A6=E1),RANDBETWEEN(1,COUNTIF(A2:A6,E1)))

CodePudding user response：

For Excel 2010 and later:

=INDEX(B2:B6,INDEX(MODE.MULT(IF(A2:A6=E1,ROW(A2:A6)-MIN(ROW(A2:A6)) {1,1})),RANDBETWEEN(1,COUNTIF(A2:A6,E1))))

which will require committing with CTRL SHIFT ENTER for earlier versions of Excel.

Note that, if you are not using an English-language version of Excel, the separator within the array constant

{1,1}

may require amending.