I would like to return all Addresses from another sheet where Column [Status] Matches a criteria

Sukh

I would like to return all Addresses from another sheet where Column [Status] matches certain "text"

I have tried two formulas and at present they only return the first instance of the address, I have generated a report, however i now need to send this new sheet on to a team member to ensure the follow up work is carried out.

these are the formulas that I currently have.

Preferably I would like one line of code to incorporate multiple search criteria selected from a multiple dropdown box (if this is too difficult the formula with two search criteria will suffice)

=IFERROR(INDEX({Site Address}, MATCH("Advisory", {Status}, 0)), " ")

=INDEX(COLLECT({Site Address}, {Status}, CONTAINS("Statuatory", @cell)), 1)

Destination Sheet

Source Data Sheet

Paul Newcome

Is the other user not able to include the follow up data on the report?

Sukh

Unfortunately not,

This sheet has over 40 columns 490 rows and the data is generated via a form by various field engineers

we are trying to automate the process so we can capture ongoing works without having to copy entire rows to other sheets, manually deleting unnecessary columns, adding more columns with checklists and approval automations before we can bill the works etc, etc...

Paul Newcome

If you give the team member editor access to both the sheet and the report (and the columns they need to fill in are in both the sheet and the report) then the user should be able to update the report.