INDEX/COLLECT or INDEX/MATCH with 2 parameters

I have tried both collect and match.

The goal is to bring the PO# from Sheet 2 to a specific cell in Sheet 1. I want the PO# when the Meeting ID on Sheet 2 is the same as the Meeting ID at the row or the column (It is the same) AND when the Vendor Type is A/V.

=INDEX(COLLECT({Vendor PO2}, {Vendor Type}, "A/V", {Meeting ID3}, [Meeting ID]@row),1)

I will use a similar formulas for Vendor Type "Court Reporter" and "Security".

Thanks!

Find more posts tagged with

Accepted answers

All comments

Shanky Paul

Did you tried with JOIN function:

=JOIN(COLLECT({Vendor PO2}, {Vendor Type}, "A/V", {Meeting ID3}, [Meeting ID]@row), ", ")

L. Barrett

Tried JOIN and it didn't work. Still got an 'UNPARSABLE' error.

Michelle Choate 2

Try with an IF Formula = IFERROR(IF([Vendor Type]@row = "A/V", INDEX({Vendor PO},MATCH([Meeting ID]@row, {Meeting ID},0)),""),"")

If you want it to put something into the column when the Vendor is not "A/V", place whatever that is in the first "" of the formula above.

L. Barrett

I am so close

Using Michelle's idea I am now here:

IFERROR(IF({Meeting ID4} = [Meeting ID (Enter This Number)]1, INDEX({Vendor PO5}, MATCH("A/V", {Vendor Type}, 0)), ""), "")

I am getting a blank or whatever I put in the second set of "".

I switched the Vendor Type and Meeting ID sections.

There are 3 other options for Vendor Type so my goal is to use this formula for all 4 Vendor Types.

L. Barrett

I have another sheet that I need to do a similar formula for. In this sheet, I want to pull the total paid based on a matching PO number and month in the Service Dates ("7/" in "7/01/24 - 7/31/24).

I have tried the IFERROR formula as well as index match contains.

Any ideas on this one?

Michelle Choate 2

Hey @L. Barrett ! Throw some time on my calendar and we can work through the formula together! My calendar link is below.

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.