How do I use the INDEX & MATCH function when trying to pull information from one sheet into another?

Ameena Bossier

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! :)

Ameena Bossier

@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

Andrée Starå

@Ameena Bossier

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!

Andrée Starå

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!

Ameena Bossier

@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

Andrée Starå

@Ameena Bossier

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!

Ian Waites

https://community.smartsheet.com/discussion/comment/254849#Comment_254849

Andre,

This helped me enormously. I wish that the formula instructions were all written this way!

Andrée Starå

@Ian Waites

Excellent!

Glad to hear that it was helpful!

Andrea Westrich

@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.

Andrée Starå

@Andrea Westrich

You're more than welcome!

Happy to hear that it's useful!

IL

+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!

Andrée Starå

@IL

Glad to hear it's helpful!

You're more than welcome!