Need Help with Index + Collect + Match Scenario

Hello all -

We are once again stuck on how to accomplish a look up to pull in data from one sheet to another. We thought we had it working, but turns out it wasn't.

We have a Source Sheet and a Receiving Sheet.

We need to pull in the Order No. from the Source Sheet to the Receiving Sheet, matching certain columns of data on both sheets, as some of the data can be duplicated on the source sheet.

Example: Bring in the Order No. for ID 01523 + that has Lunch checked, we need to match the following columns on both sheets:

ID_Helper + Lunch + Project No.

The trick is that on both sheets the ID No. will have multiple occurrences with Lunch or Dinner checked, with the same or different Project No's.

Its a situation of "look at all of these columns and find the ones that match up, and bring in that Order No.". We just haven't been able to figure out how to accomplish it with a formula.

Test Sheets are linked below:

Test Sheet J (receiving): https://app.smartsheet.com/b/publish?EQBCT=4278cd51fe224246abd59671114a534e

Test Sheet K (source): https://app.smartsheet.com/b/publish?EQBCT=cbd6a20d0522421a853849658431bc63

Any help anyone is able to offer will be greatly appreciated. Thank you! ☺️

Find more posts tagged with

Accepted answers

Nancy Heater

Good morning @Victoria_Indimar !

In our actual Source + Receiving sheets, there are some other columns in-between the ID and Dinner columns. However, I was able to take your same idea but used this:

=[ID_Helper]@row + " " + [Project No]@row + " " + Lunch@row + " " +Dinner@row

and it appears to be working!

Thanks for pointing me in the right direction!

All comments

emilynduran

Does it have to be a formula? I feel like you could possibly do this easier with automation.

Victoria_Indimar

Hello!

ID_Helper could be a combination of those fields you're looking for in both sheets: =JOIN([ID]@row:[Lunch]@row,"+")

Then your Order No formula in the receiving would be: =INDEX({Source_Order No},MATCH({Receiving_ID_Helper},{Source_ID_Helper},0),1

Is that what you were looking for? Does that work for you?

Nancy Heater

Good morning @Victoria_Indimar !

In our actual Source + Receiving sheets, there are some other columns in-between the ID and Dinner columns. However, I was able to take your same idea but used this:

=[ID_Helper]@row + " " + [Project No]@row + " " + Lunch@row + " " +Dinner@row

and it appears to be working!

Thanks for pointing me in the right direction!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.