INDEX & Match

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

Find more posts tagged with

Formulas

Accepted answers

All comments

Andrée Starå

Hi @PeggyLang

I hope you're well and safe!

You'd use the COLLECT function,

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

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

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_range2criterion2... —[optional] Additional ranges and criteria to have evaluated. All criteria must be met to be collected.

Hope this helps,

Cheers!

Ipshita

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.