Pulling Partial Rows Using Index/Match

cholt24

Hi all,

I'm hoping someone can point me in the right direction here. I'm trying to figure out the best way to pull partial rows from one sheet to another without using Bridge. Specifically, I'm trying to pull requests that were populated on a separate grid sheet through a form over to the department-wide tracker grid sheet. The sheets have different columns, so I can't pull the rows over directly without creating unnecessary columns.

I've heard that you can create a workaround by using Index/Match to pull partial rows/multiple from the source sheet into a helper sheet and then pull certain values from that helper sheet into the destination sheet.

Has anyone built this workaround before? Tips, links to tutorials, or feedback would be more than welcome!

AravindGP

Hi @cholt24

You will first need to have one unique identifier in both the source and the destination sheet. Once you have it, you can use index/match formulas in the other columns to pull the data. A sample formula will be =INDEX({column from which you need the value to be pulled}, MATCH([Column in destination containing the search value]@row, {column from source which have the search value}, 0).

You will replace the index column part of the formula for each column in your destination sheet that needs to pull the value from source, keeping the match part of the formula exactly the same.

cholt24

Hi Aravind,

Thank you for providing this rundown. The destination sheet I am using is currently blank. What I was trying to do is pull specific data sets from the source/form entry sheet into a final destination sheet, but the columns in the source sheet do not match my final destination sheet. I'm using a helper/destination sheet to bridge that gap, so those rows would move as follows:

source/form entry sheet → (index/match)→ helper/destination sheet → (automated workflow)→ final destination sheet.

I set up an autonumber column both the source/form entry sheet and the helper/destination sheet to be the unique identifier, but that won't work since nothing is triggering the autonumber column to create the linking unique identifier on the helper/destination sheet.

Is there something else I can do to pull that data over to another sheet other than using Bridge or manually entering a unique identifier on my helper/destination sheet? Any and all feedback would be very appreciated!

Adam Murphy

Data Mesh is made to do what you are describing.

As Arvind said earlier, you have to have that unique key in both sheets to use index/match. If you want to use the Row ID from the source sheet you could add a column to your destination sheet and pre-populate that value down a bunch of rows (up to 20k), that gives you the unique value to use in the formula, then you just bring over the fields you want that way.

Why the middle helper sheet? Why not pull these values directly into the final sheet?