Need help with this formula


=IF(({New Employee Training Process Range 1}=ISTEXT"BU East"), {New Employee Training Process Range 3})

Trying to get it to find matches from a column in one sheet and a column in the second sheet. Wanting it to return the value of another column in the same row for each time it finds a match.



  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭

    Any way you can post an example of your sheet? It's a little hard to help with the formula based on the info above.

    One note, your current IF formula isn't finished, which would give you an error. If the Range 3 reference is what you want returned, add @row to the end of it and finish off the formula with what you want if it doesn't match:

    IE: =IF(({New Employee Training Process Range 1}=ISTEXT"BU East"), {New Employee Training Process Range 3}@row, "-")

    I also don't think you need the ISTEXT function. You could just do = "BU East".

    =IF(({New Employee Training Process Range 1}="BU East"), {New Employee Training Process Range 3}@row, "-")

  • Mel.
    Mel. ✭✭

    Use Index/Match:

    =Index([cross reference to column in other sheet containing the value you want returned], Match([cell in this sheet you want to match to], [cross reference to other sheet the column with the matching value], 0))

    My drawing I attached is how I did it once, and I keep it around to keep myself straight. Essentially, I had a listing of training courses on one sheet that had date, location, etc.(labeled MGS in diagram), and I wanted that info in another sheet that listed the courses but was missing that info. Instead of manually copy/pasting, this formula looked at the EventID (both sheets had it - unique value for each class) on the same row, then went to MGS, found the matching EventID (the column was titled EMSID on the MGS), and pulled back the start date. Then I did the same formula in another column for End Date, and again for Location. Boom!

    Hope this was helpful!! 😄

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!