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)
