How to randomly select cell and the adjacent cell on excel

0
votes

I am trying to find a formula on excel that will allow me to randomly select a cell between a range (this part I have found a formula for) and have the value pasted into another cell AND then have the cell next to the randomly selected cell's value also selected and pasted into a different cell.

Not sure if this makes sense so I will explain what I'm trying to do.

I have a column full of years and the adjacent column full of prices that correspond to each year. I want a year randomly selected and pasted into a cell lower down on the spreadsheet, and I want the corresponding price for this randomly selected year pasted next to this cell.

Thank you in advance!

Anna

1
excelformula

1 Answers

1
votes

Say the data is in A1 through B10

In D1 enter:

=INDEX(A1:A10,RANDBETWEEN(1,10))

and in E1 enter:

=VLOOKUP(D1,A1:B10,2,FALSE)

enter image description here

Another approach is to enter:

=RANDBETWEEN(1,10)
=INDIRECT("A" & C1)
=INDIRECT("B" & C1)

In C1 through E1

(Either approach will work if column A contains years rather than names)

Yet another approach is to enter:

=RANDBETWEEN(1,10)
=INDEX(A1:A10,C1)
=INDEX(B1:B10,C1)

In C1 through E1