INDEX & Match

PeggyLang
PeggyLang ✭✭✭✭✭✭

I have what I am calling a 'Lookup' Worksheet.

I also have a Change Order Log, an Invoice Log and a Contract Log.

I would like the 'Lookup' worksheet to return all Change Order(s) info, Invoice(s) info and Contract info.

Column 'PC #' is hidden and each of the columns to the right of 'PC #' contain Index & match functions.

Currently this works PERFECTLY when there is only 1 Change Order, or 1 Invoice.

How do I get the 'Lookup' worksheet to pull all the Change Order info (if more than 1 Change Order) and all the Invoice info (if more than 1 Invoice).

Tags:

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @PeggyLang

    I hope you're well and safe!

    You'd use the COLLECT function,

    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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.

  • PeggyLang
    PeggyLang ✭✭✭✭✭✭

    @Andrée Starå Thank you for responding!

    I'm not sure how to incorporate the COLLECT (that's a new formula for me) into the formula I have

    =INDEX({Change Order Log - APPROVED Description}, MATCH([PC #]@row, {Change Order Log - APPROVED PC #}, 0))

    What I need is for the Change Orders and Invoices to be listed on separate rows.

    i.e., if PC # 1234 has 3 more change orders then I would want a row for Change #2, Change #3 and Change #4. At the same time if there were more than 1 invoice then the 2nd invoice should be listed on the row with Change #2 and the 3rd invoice should be listed on the row with Change #3.



    I'm hoping this makes sense?

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hi @PeggyLang ,

    @Andrée Starå is 100% correct here. Your formula would go in combination with the COLLECT function.

    See the scenario and example below for using the COLLECT function.

    COLLECT( range criterion_range1 criterion1 [ criterion_range2​criterion2​... ])

    • range — The group of values to collect.
    • criterion_range1 — The group of cells to be evaluated by the criterion.
    • criterion1 — The condition that defines which values to collect.
    • criterion_range2​criterion2​... —[optional] Additional ranges and criteria to have evaluated. All criteria must be met to be collected.


    Hope this helps,

    Cheers!

    Ipshita

    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!