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!
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.
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!