INDEX & MATCH using multiple Columns - Formula help
I just can't wrap my head around this one. I have read a lot about INDEX and Match but I have not been able to make this one work.
Heres what I need to do
- When the profile and finish match on another REF sheet,(The same setup with 2 columns) it will give me a value for my Formula cell. (Just need to copy over to this sheet)
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
Best Answer
-
Excellent!
Glad you got it working!
✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi,
You need a unique identifier that you can match up on both sheets, like system row ID column that you can hide - then on the sheet that needs to pull the data in, you manually fill in the next unique identifier (like the row ID) and then for each column try adapting this formula:
=IFERROR(INDEX({sheet name that will act as your range}, MATCH($[Unique ID]@row, {Unique ID}, 0), MATCH([Profile]#, {sheet that will act as your source Header Row}, 0)), "")
The items in bold is what you need to replace with your relevant sheet/field names. In my example I made use of Sheet summary field to reference in the formula, hence the: [Profile]#
Hope this can be of some help.
-
I hope you're well and safe!
To add to Marcelle's excellent advice/answer.
- I'd recommend adding a so-called helper column on each o the sheets where you can combine the columns with the values you want to compare with either a JOIN formula or by adding them together with +.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Marcelle.
So there is no way to match 2 columns and then pull 1 value over?
The issues I have is I need both columns to be drop downs, When the profile matches a finish, I need it to reference a number and then pull it to this sheet so I can do other formulas with it.
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
I got it to work, I ditched it all and did a VERY long IF Statment and it did what I needed it to do, Thanks!
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
Excellent!
Glad you got it working!
✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!