FILTER formula based on field with numbers and text formats

[Excel Novice 1]

Hi,

I have a list of Product and Location numbers and want to display a list of unique Product numbers that start with the number 8 at a subset of locations (from list). My first approach was to use a FILTER formula nested within a UNIQUE formula and set the filter criteria to a list of the locations that I want to display. However, I'm facing a challenge since the location identifiers contain a mix of numbers and text and I can't figure out how to get the Filter function to work with both formatting types in the same column.

I am thinking that the addition of an intermediary step may help. The step would indicate if the location code is in the location list using "1"/"0" and would be a way to convert the column with number/text formatting to just number formatting. Then I can run the filter formula off the new column. However, I wanted to check if there was a way to do this without the intermediary step.

Example dataset:

Book1
	B	C	D	E	F	G	H	I
3	Product ID	Location ID		Locations to include		Product ID to include		Expected result
4	85221	852		852		Start with 8 only		85221
5	35440	115		C223				84524
6	85325	A354		115				89638
7	84524	852
8	89638	C223
9	96529	115
10	33544	115
11	33547	A354
12	84524	852	<<duplicate
13	89638	C223	<<duplicate
Sheet1

 

[keda duck]

text.xlsx
	A	B	C	D	E	F	G	H
3	Product ID	Location ID		Locations to include		Product ID to include		Expected result
4	85221	852		852		Start with 8 only		85221
5	35440	115		C223				84524
6	85325	A354		115				89638
7	84524	852
8	89638	C223
9	96529	115
10	33544	115
11	33547	A354
12	84524	852	<<duplicate
13	89638	C223	<<duplicate
Sheet8

Cell Formulas
Range	Formula
H4:H6	H4	=UNIQUE(FILTER(A4:A13,COUNTIF(D:D,B4:B13)*(LEFT(A4:A13,1)="8")))
Dynamic array formulas.

If your Office Version is 365, it would be more convenient than 2010...