Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Join row data formula

✭✭✭✭✭
edited 10/09/24 in Formulas and Functions

I have two sheets for a traning evaluation form. They both have a common column, TrainingID. Based on that column I want to bring in data from one sheet to another. Example: For all rows that have BLS05 as a Training ID, I want the data columns from Facilitator Information to merge with the Evaluation sheet

Facilitator Information will have the training information such as Trainer, Title, Date, Training ID and the Evaluation sheet will have all of the responses to the questions ask and the same training ID.

My attempt to create an Index Match is this: #UNPARSEABLE error

=INDEX({Facilitator Data Form Range 2}, MATCH({Facilitator Data Form Range 1}, ([TrainingID@row])))

The Facilitator date range are the columns I want to merge into the Evaluation sheet that has the responses. Each response will have a TrainID - common per training. So there could be 25 rows with the same TrainIDs of BLS05.

Am I on the right path? How do I fix the UNPARSEABLE error?

Tags:

Answers

  • Community Champion
    edited 10/09/24

    Hi @rachmavis

    I sometimes make mistakes as well, but you have to put search_value first in the MATCH function.

    =INDEX({Facilitator Data Range}, MATCH([TrainingID]@row, {Facilitator TrainingID Range}, 0))

    Syntax

    MATCH(search_value, range, [search_type])
    

    search_value— The value to search for.

    range— The cell range (lookup table) to be searched.

    search_type—[optional] The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).

    Site faviconSmartsheet

    Site faviconSmartsheet

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions