Indexing and collecting information from non adjacent columns in original sheet

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

All comments

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

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

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

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?

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

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

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

Kelly

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")))