Help With Index-Collect and Cross Sheet Referencing

Mitchell S.

Hi all,

I am attempting to return a Contractor's name from a source sheet using multiple criteria (if they are subcontracting and what their scope of work is). The issue I am running into is the unique ID I use to connect the sheets (The PCM Number) has duplicate values when there are subcontractors (multiple contractors using the same PCM number since it is the same project). I initially used the following index match formula to pull the data, however I found it did not work as the match formula only pulls the first instance and stops there. I can use this formula when there are no subcontractors, however I realized I needed to create a different formula for the projects with subs.

=IFERROR(IF(AND(INDEX({Subcontractor}, MATCH([PCM Number]@row, {Contractor PCM Number}, 0)) = "Yes", INDEX({Contractor Public Improvements}, MATCH([PCM Number]@row, {Contractor PCM Number}, 0)) = "Paving"), INDEX({Contractor Resident Agent Address}, MATCH([PCM Number]@row, {Contractor PCM Number}, 0))), "")

After searching around, I attempted to use an Index-Collect function, however I am running into the issue where it is pulling the same contractor name across the entire column instead of what is should match to. I would think I would have to nest it in an IF function, but I am not quite sure on how to go about that. The easy solution is to create multiple sheets for each subcontractor scope of work which is what I was originally doing, but I am trying to reduce the amount of forms the contractors need to fill out. Any help would be greatly appreciated.

=INDEX(COLLECT({Contractor Legal Name}, {Subcontractor}, "Yes", {Contractor Public

Improvements}, "Utilities"), 2)

Source Sheet - Columns Referenced.PNG

Target Sheet - IndexCollect.PNG

Adam Murphy

Hi @Mitchell S. I think INDEX/COLLECT is the way to go. Why are you choosing 2 for the row index? Have you tried that as a 1? Note that if this works it will return the first Contractor it finds where Subcontractor is Yes and scope is Utilities. You may also want to match on PCM number? You can add that to the Collect just as you did the other 2 criteria.

Hope this helps!