VLOOKUP Query

JohnPoole

Active Member
Joined
Jun 9, 2005
Messages
254
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
Joined
Jan 21, 2012
Messages
912
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
Joined
Jun 9, 2005
Messages
254
I should add I also tried entering as an array formula with CTRL+Shift+Enter and got #N/A .
 

JohnPoole

Active Member
Joined
Jun 9, 2005
Messages
254
Ignore! That's fixed it actually - my klutsty typing. Thanks for the help!
 

Forum statistics

Threads
1,082,610
Messages
5,366,601
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top