How to Use Data from One Sheet in Another with Cross-Reference List and Index Formula?

System

This discussion was created from comments split from: How to Index Match.

Pauline J

Hello @Vika Velickaite

I didn't see the previous posts, but … it sounds similar to a project I manage where I connect to another department's sheet, and need to pull in the data. It was a little bit to setup, but they agreed to create an autonumber column, giving us a unique identifier for each row. Then, in my sheet, I setup the same column, replicating their numbering format. This way, every row in their sheet has the same unique idenfier in my sheet rows.

In the sample image, I included their column number in my Column name. You can see that I didn't need all of their data, just some of it.

The formula (converted to column formulas)

=VLOOKUP(RowID@row, {NameOfTheirSheet Range 1}, 3, 1

The range is their entire smartsheet. The number "3" is the number of the column, from left-to-right, that will populate your sheet.

You have to have "empty" numbered rows in your sheet so that when they add new rows, their new rows will match into yours. I just added about 300 rows, which will last me a long time.

Example:

The other important thing, is that the owner of the sheet you are pulling from cannot alter the order of the columns! Because Column number 3 is just that — if Column 5 moves over to become Column 3, you'll get Column 5's cell contents — and all the rest of your columns will "slide" as well. What my collegues do is add new columns if they are needed, to the far right of the sheet, so the column numbers do not change.

I've also done the same thing but using index/match:

=IFERROR(INDEX({TheirFile Range 1}, MATCH([Row ID]@row, {TheirFile Range 2}, 0)), " ")

Each column also has a unique formula, because the Range 1 is the column you want to return into your column. BOTH methods require that original file columns stay in order.

I hope this makes sense — and I hope it helped! If someone else has a better solution, I'd love to learn about it.