How do I use the INDEX & MATCH function when trying to pull information from one sheet into another?
Hey All,
Reaching out to the masses to hopefully get some help. I need to pull information from sheet 1 into sheet 2 using the INDEX and MATCH functions.
Sheet 1 that I want to pull information from has:
- Child rows
- Task Name column (The list of tasks have the same name in both sheet 1 and sheet 2)
- Actual Date column
What I want to show up in sheet 2
- The Actual Date column information
I’ve tried the following and keep getting “UNPARSABLE”, blank cells, or “NO MATCH” as the result
- =INDEX({Actual Date}, MATCH([Exercise Name]@row, {Exercise Name}))
- =INDEX([Actual Date]:[Actual Date], MATCH(Exercise Name@row, [Task Name]:[Task Name], 0))
- =INDEX({Actual Date}, MATCH([Exercise Date]@row, {Exercise Name}))
- =INDEX([{Actual Date}]:[{Actual Date}], MATCH([Exercise Name]@row), [{Exercise Name}]:[{Exercise Name}], 0))
- =INDEX({Exercise Name}, MATCH([Exercise Name]@row, {Actual Date}))
I’ve gotten this info from Smartsheet
- Combining INDEX and MATCH in the same formula you will be able to pull up text value as well as numeric values. The INDEX function will be used as the reference of the column of the value to return whereas the MATCH function will be looking at the value to match it with.
- An example of the formula would look similar to the following: =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))
- https://help.smartsheet.com/function/index
- https://help.smartsheet.com/function/match
- https://help.smartsheet.com/function/children
I can’t figure out what I’m missing! HELP PLEASE! :)
Best Answers
-
@Andrée Starå - Apologies for the overdue response! Your solution worked! I also found that I need to make sure the column properties were the on both sheets. That will lead to an error if they aren't.
Thanks again! :)
-Ameena
-
Excellent!
You're more than welcome!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as 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 Ameena,
=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; {ColumnWithTheValueToMatchAgainsTheCell}; 0))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Make sense?
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 help the Community by marking it as 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.
-
@Andrée Starå - Apologies for the overdue response! Your solution worked! I also found that I need to make sure the column properties were the on both sheets. That will lead to an error if they aren't.
Thanks again! :)
-Ameena
-
Excellent!
You're more than welcome!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as 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.
-
Andre,
This helped me enormously. I wish that the formula instructions were all written this way!
-
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.
-
@Andrée Starå I second your creative way to type out the formula. For beginners or those not overly familiar with formulas, this is incredibly helpful in ensuring it can be used on any sheet or report! Thank you so much for the simplicity.
-
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.
-
+1,000,000,000 on the cheat sheet - was going insane trying vlookup and other ways to bring in status updates to another tracker - thank you a ton!
-
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
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!