Expanding a list to repeat values based on another column

[ryangodammit]

Hi,

I have a list of addresses and names.

Down one side I have numbers which correspond to properties on another worksheet. Each number appears at least 3 times. On the same page is another column of the same numbers, which don't repeat. I would like to take the suburb name from next to the non repeating numbers and populate the column next to the corresponding numbers which repeat themselves.

Is this possible?

Let me know if I didn't make sense and I'll try and clarify.

Thanks so much
Ryan

[bluesky63]

Hi,

don't quite understanding what you want to achieve, and you uploaded spreadsheet number column values not all appeared 3 times, without seeing your another worksheet, I can't visual it

are you able to indicate in another worksheet on the final result that you want to achieve and explain it came from which source range/worksheets

Rgds

[ryangodammit]

Hi Christopher,

Thanks for the reply.

Each suburb in the property suburb column has a number assigned, under the property number column are the same numbers, although generally there are about 3 of each number.

I would like to put the suburb corresponding with number 1 in the blank column next to all "Property Numbers" that also have one. In this spreadsheet it would be the three top entries C2,C3, and C4.

Hope this makes it a little clearer.

Ryan

[bluesky63]

Hi Ryan

in A2, input formula =INDEX(G:G,MATCH(B2,F:F,0)) and drag down

Not sure if this is what you want, if not give us the desire result in the column/cell

[ryangodammit]

That is exactly it!

What is that formula?

[bluesky63]

Hi Ryan,

INDEX() returns the value of a cell in a table based on the col and row no.

G:G is the returning column of Property Suburb that you want,
MATCH() command helps you to match each cell in the number column with Column F ( the unique number col)

e.g. for Cell G7, Row number = 2, Col number = 7 [Row() and Column() are the command to find the Row/Col no of a cell]


you can google Index/Match and there are quite a number of explanation, examples, video that you can refer to, Index/match is the same as Vlookup, however Vlookup cannot look up value on the left, while Index/match is the better combination

Cheers !