Excel help – filtered list for data validation

I’m trying to engineer a data validation list which needs to be derived from Colum A (department name starting with 6 digit code) based on a choice from Column B (the 6 digit code). So user selects a 6 digit code, then has a resulting drop down of department names starting with that 6 digit code.

Starting point was a Vlookup but there can be more than one department using a given 6 digit code, so a vlookup only finds the first one.

I tried to use a filter(columnA, columnb(first 6 digits)) but no joy. I also tried including my formula here but website says no (to code presumably).

Any Excel brains able to help?

google xlookup, it can bring back a table of results so might be closer to what you are looking for.

Pivot table

I’ve frigged it to just lookup the department name, then extract the code, which is then used to bring back the additional details. All sorted, panic over, thanks.