Index or Vlook-up for several rows of data

Beth Klineman

I am trying to pull in all columns of data from my "source sheet" into my "destination" sheet. The source sheet has about 2000 ID row entries and 34 columns of data for each entry. The destination sheet will be 5-10 ids. When you input that ID on the destination sheet I want the destination sheet to pull in all 34 columns of data from the source sheet row. Is it possible to do a range of column data to pull into the destination sheet from the source or will I have to index each column individually for all 34? The data in most columns is either text or checkbox (I converted the checkbox columns to text and now show with true or false) All columns in each sheet are labeled the same

Here is what I thought would work:

=INDEX({Source Sheet Full Range}, MATCH([ID Input]@row, {Source LO ID}, 0))

translation =INDEX({2000 rows of 34 columns which "should" populate with the full data from the ID row in the source sheet}, MATCH([user input of a specific ids]@row, {ID column in source sheet matching user input ID}, 0))

References

1. Source sheet full range = all 34 columns in the refence sheet pop-up are selected

2 . ID input — a user types in the id in the destination sheet

3. Source LO ID = only the the LO column selected in the refence sheet pop-up

Source Sheet

Destination Sheet

What I assume should happen in the destination sheet when a user types in the ID in the ID input column all 34 columns of data should populate from the source sheet but it is not happening I am getting an invalid column value error.

Melissa Yamada

@Beth Klineman

As there is no direct automation option that will look at the destination sheet.

A possible solution is:

Step 1: Create a helper sheet. This will be the sheet where you'll add the ID you want to pull on your destination sheet.

Step 2: Add a helper column (checkbox) on your source sheet with an INDEX/MATCH or INDEX/COLLECT formula that will check the box of the row that matches the ID you add on your helper sheet on Step 1.

Step 3: Create your automation that when rows are added or changed with a condition of where the helper column (checkbox) on your source sheet is checked then the action will be to move or copy the row to your destination sheet.

Melissa Yamada

Hello @Beth Klineman

You'll need to index each column individually.

A possible workaround is to use the copy row automation that will copy the row where ID is equal to either of the 5 to 10 IDs you mentioned.

Beth Klineman

@Melissa Yamada how would I create that? When I look at automation the source sheet data would have to be changed or added or need a trigger. I want to use a look-up when an id is typed into the destination sheet all columns from that row are pulled in from the source sheet. I have searched help for the copy row function to another sheet but am obviously misunderstanding something.

Humashankar

Good insight

Melissa Yamada

@Beth Klineman

As there is no direct automation option that will look at the destination sheet.

A possible solution is:

Step 1: Create a helper sheet. This will be the sheet where you'll add the ID you want to pull on your destination sheet.

Step 2: Add a helper column (checkbox) on your source sheet with an INDEX/MATCH or INDEX/COLLECT formula that will check the box of the row that matches the ID you add on your helper sheet on Step 1.

Step 3: Create your automation that when rows are added or changed with a condition of where the helper column (checkbox) on your source sheet is checked then the action will be to move or copy the row to your destination sheet.