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! ☺️
Best Answer
-
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!
Answers
-
Does it have to be a formula? I feel like you could possibly do this easier with automation.
-
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?
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!