# VLOOKUP Query

#### JohnPoole

##### Active Member
Hi all, I'm looking for some help to randomize a VLOOKUP. I have four values on column A, all Zero. Column B has values A,B,C,D The VLOOKUP returns A as this is the first matched value.

The VLOOKUP correctly returns the result of the minimum value in column A:
=VLOOKUP(MIN(A1:A4),A1:B4,2,0)

Is there a way to randomly to return any of the other possible results which are joint lowest, rather than just returning the first matched value everytime?

Any help appreciated,

Thanks,

John

#### theBardd

##### Rules violation
This array formula is a bit convoluted, might work for you

Code:
``=INDEX(B1:B6,LARGE(IF(A1:A6=MIN(A1:A6),ROW(A1:A6)),RANDBETWEEN(1,COUNTIF(A1:A6,MIN(A1:A6)))))``

#### JohnPoole

##### Active Member
This array formula is a bit convoluted, might work for you

Code:
``=INDEX(B1:B6,LARGE(IF(A1:A6=MIN(A1:A6),ROW(A1:A6)),RANDBETWEEN(1,COUNTIF(A1:A6,MIN(A1:A6)))))``

Thanks for this, but I'm getting a #NUM ! error....

#### JohnPoole

##### Active Member
I should add I also tried entering as an array formula with CTRL+Shift+Enter and got #N/A .

#### JohnPoole

##### Active Member
Ignore! That's fixed it actually - my klutsty typing. Thanks for the help!