Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

INDEX/COLLECT or INDEX/MATCH with 2 parameters

edited 01/30/25 in Formulas and Functions

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!

Answers

  • ✭✭✭✭✭

    Did you tried with JOIN function:

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

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

  • Community Champion

    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.

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

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

  • 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?

  • Community Champion

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

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions