Indexing and collecting information from non adjacent columns in original sheet

Rachael Stammers

I want to pull information from one sheet into another by index match.

The formula has to index the information from either F1 IC / Recommendation, F2 IC / Recommendation or F3 IC / Recommendation by searching for/finding either Recommendation 1 ID No., Recommendation 2 ID No. or Recommendation 3 ID No.

These columns are not adjacent to each other in the origin sheet.

Is this possible?

OR

OR

All assistance greatly appreciated.

Rachael

Kelly Moore

Hey @Rachael Stammers

Unparseables are typically caused by wrong column names, misplaced commas, and/or missing brackets.

You have two parentheses in between each IFERROR and INDEX. I think this is the problem

If that isn't it, are the references on your field name 'Recommendation ID No in colored text when you look at the formula? If no, copy the name from the column header and paste it directly into the formula, overwriting the row references already there. Be sure the '@row' portion still remains.

Kelly

Kelly Moore

Hey @Rachael Stammers

Yes, it's possible. We can utilize the #NOMATCH error when the Match fails and the IFERROR function, which allows you to specify what to do when an error occurs.

You cannot just copy paste this formula into your sheet. You must create the cross sheet references using the Insert reference link in the formula window.

=IFERROR(INDEX({F1 IC / Recommendation}, MATCH([Recommendation ID]@row, {Recommendation 1 ID No.}, 0)), IFERROR(INDEX({F2 IC / Recommendation}, MATCH([Recommendation ID]@row, {Recommendation 2 ID No.}, 0)), IFERROR(INDEX({F3 IC / Recommendation}, MATCH([Recommendation ID]@row, {Recommendation 3 ID No.}, 0)), "No Match")))

If there are no matches found in any of the three ranges, the "No Match" will be entered instead of leaving the error #NoMatch. You can put anything in its place.

Will this work for you?

Kelly

Rachael Stammers

Hi @Kelly Moore Thank you. I'm going to try it now and let you know how it goes.

Kelly Moore

Hey @Rachael Stammers

I made the assumption that you had an ID Number column on your target sheet so you could perform the match. This is the basis of an index/match. You'll need to change the name of my placeholder column with the name of your actual Target sheet ID Number column.

@mention me if you have any problems or questions

Kelly

Rachael Stammers

HI @Kelly Moore

I have been trying to get this to work, but it's coming back as Unparseable

This is the formula I've been putting in, according to what you provided

=IFERROR((INDEX({3. Safety Investigation Range 5}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 6}, 0)), IFERROR((INDEX({3. Safety Investigation Range 7}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 8}, 0)), IFERROR(INDEX({3. Safety Investigation Range 9}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 10}, 0)), "No Match")))

Can you see any glaring errors?

Kelly Moore

Hey @Rachael Stammers

Do you have the column Recommendation ID No in your target sheet? I used the name as a placeholder for your real column name.

Kelly

Rachael Stammers

Hi @Kelly Moore

I do. this is the target sheet where I'm wanting to pull the information into

And this is the source sheet

Kelly Moore

Hey @Rachael Stammers

Unparseables are typically caused by wrong column names, misplaced commas, and/or missing brackets.

You have two parentheses in between each IFERROR and INDEX. I think this is the problem

If that isn't it, are the references on your field name 'Recommendation ID No in colored text when you look at the formula? If no, copy the name from the column header and paste it directly into the formula, overwriting the row references already there. Be sure the '@row' portion still remains.

Kelly

Rachael Stammers

Good Morning @Kelly Moore

It worked 😁 Thank you so much. This has saved me so much time with capturing data,

=IFERROR(INDEX({3. Safety Investigation Range 5}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 6}, 0)), IFERROR(INDEX({3. Safety Investigation Range 7}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 8}, 0)), IFERROR(INDEX({3. Safety Investigation Range 9}, MATCH([Recommendation ID No]@row, {3. Safety Investigation Range 10}, 0)), "No Match")))

Kelly Moore

Hey @Rachael Stammers

So glad you got it working.

As you continue to grow your formulas, consider the good practice of renaming the generically assigned cross sheet reference names from smartsheet. For instance, instead of 3. Safety Investigation Range 7, you can rename the Range 7 before you click the insert reference button to reflect the name of your actual column. This will help you troubleshoot your formulas as you can see by the range name more clearly in the text.

Let me know if there's anything else I can do for you

Kelly