INDEX & Match

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

Andrée Starå

Hi @PeggyLang

I hope you're well and safe!

You'd use the COLLECT function,

COLLECT Function | Smartsheet Learning Center

https://help.smartsheet.com/function/collect

Used within another function to collect specific values in a range that meet the provided criteria.

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!

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

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