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! ☺️
Help Article Resources
Check out the Formula Handbook template!