Thread: match data

In C1: =INDEX($I$2:$R$11,ROW(),MATCH($A$2,$I$1:$R$1,0))

Copy the formula to C11 and adjust the reference from $A$2 to $A$3 and so on, adjust the ROW() to ROW-10, then ROW-20, etc as you move down the column. Optionally, for each of the ten cells you could use and copy then down, adjusting the $A$2 as noted:

=INDEX($I$2:$R$11,1,MATCH($A$2,$I$1:$R$1,0))
=INDEX($I$2:$R$11,2,MATCH($A$2,$I$1:$R$1,0))
=INDEX($I$2:$R$11,3,MATCH($A$2,$I$1:$R$1,0))
etc.

Palmetto thanks for your help.
But before i close this i want to try and complicate things a bit.if in a2 i pick a house then c1 will choose a random record from there list.a3 i pick a house c2 will display a random record from there details and so on.And same applies again when i change a2 to a different house.thanks again.

You could use the RANDBETWEEN function to randomly select the row from which to pull the record.

However, anytime you make a different selection in any of the drop downs it will trigger the function to generate a new random number. There is no way to avoid the issue other than either using VBA or turning off automatic calculation.


=INDEX($I$2:$R$11,RANDBETWEEN(1,10),MATCH($A$2,$I$1:$R$1,0))