Formula to return matching value

Options
Gloria H.
Gloria H. ✭✭✭
edited 10/20/23 in Formulas and Functions

Hi! I'm looking for help with a formula that will return the corresponding row value in [Dan Accrual] column when the value of the [Period] column is "Y". I was thinking this should be an index/match, but I can't quite puzzle it out. I'd be thrilled to have this result in the sheet summary, and not need to utilize an additional column.

Thanks!



Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Gloria Perez@Gloria H. you should be able to reference this with this formula:

    =INDEX(COLLECT([Dan Accrual]:[Dan Accrual], Period:Period, "Y"), 1)

    The collect function will give you all instances where the Period is "Y", and the "1" at the end of the Index will provide the first instance. If it's an exact match and only 1 instance exists, then you will get that one.

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Gloria Perez@Gloria H. you should be able to reference this with this formula:

    =INDEX(COLLECT([Dan Accrual]:[Dan Accrual], Period:Period, "Y"), 1)

    The collect function will give you all instances where the Period is "Y", and the "1" at the end of the Index will provide the first instance. If it's an exact match and only 1 instance exists, then you will get that one.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!