VLOOKUP Query

[JohnPoole]

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]

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

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

 

[JohnPoole]

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

=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]

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

 

[JohnPoole]

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

 

[Sumanmathew]

PLEASE HELP ME FIX THIS

 

[Fluff]

@Sumanmathew
Please start a new thread for your question, When you do, you will need to give a lot more information. Thanks

 

[Sumanmathew]

@Sumanmathew
Please start a new thread for your question, When you do, you will need to give a lot more information.

@Sumanmathew
Please start a new thread for your question, When you do, you will need to give a lot more information. Thanks

Okay.Thankyou.