Look up multiple matches in list and return multiple values

Hi,

I have a sheet with a source dataset in a table, with columns POSTCODE and LGA (Local Government Area).

On a second sheet I want to enter a list of LGAs in column A. I'd then like all rows matching those LGAs to populate in columns B and C (POSTCODE and LGA), with no blank rows.

There will be multiple rows with the same POSTCODE and LGA, as the unique data point in the source data is LOCALITY (unused), so I may need to manually remove duplicate rows based on POSTCODE, but I'd love if this could be done automatically.

Attachments
Screenshot 1 is an excerpt of the source dataset
Screenshot 2 is a manually created example of the desired output (Manually input to column A, return info in columns B and C)
Screenshot 3 is the same as 2 but with the duplicated entries removed - Ultimately desired outcome.

Really grateful for any advice that can be given! Thanks!

Easiest way to do this is by using PivotTable with filter/slicer.

Thanks for your response Joseph. I'm not experienced with Pivot tables - Could you give me some step-by-step instructions?

Please attach sample workbook. Read instructions on how to attach workbook on the yellow panel at the top.

Sample workbook attached

Attached file with Postcode and LGA Region in Rows area of PivotTable and LGA Region in Slicer.

Thank you, this seems to work very well, except that I appear to need to manually select the LGAs. This is essentially what I was doing previously - Filtering a table by manually selected LGAs (and also manually removing duplicates - So pleased to see this does so automatically!). I would however like the filter to use a copied list.

Also, the dataset in the example is only an excerpt for the purposes of sharing here. I've tried to add some of the omitted rows to test, and the table has automatically expanded, but the slicer does not include the other options. Can you advise how to update your example with the full dataset of 18407 rows?

Thanks again for your help!

You need to right-mouse-click and refresh the PivotTable, whenever new data is added.

Thank you, that has worked.

And regarding the need to manually select LGAs, rather than entering a list of LGA names and the pivot table using that to filter?

Hi, still looking to a solution to the above so I can mark as Solved. Thank you

Can't do with PivotTable. Sorry, I can't think of a formula. Hopefully some experts can come in to help.