Spaces in a searchable dropdown

[Ztcollins]

I have made a drop down list that is searchable using formulas and it works. However if i type in 2x4 it gives me certain items. If i type in 2 x 4 with spaces it gives me different items.

=IFERROR(VLOOKUP(ROWS($I$2:I2),$C$2:$D$3799,2,0),"") this one looks to see if there is text from what is typed in the search box

=IF(ISNUMBER(SEARCH(Sheet1!$K$4,'RE PARTS'!D2)),MAX($C$1,C1)+1,0) this one looks at the search text and gives a number to each one that contains the text.

=OFFSET($I$2,,,COUNTIF($I$2:$I$3799,"?*")) counts if there is a text in the empty cells

=COUNTIF(I2:I3799,"?*") this is a count to just show how many results are in the dynamic array.

Is there a way to make the search ignore spaces?

Thanks in advance for any help!!

 

[AlphaFrog]

Maybe this...

SEARCH(SUBSTITUTE(Sheet1!K4," ",""),'RE PARTS'!D2)

 

[Ztcollins]

Maybe this...

SEARCH(SUBSTITUTE(Sheet1!K4," ",""),'RE PARTS'!D2)

Inserted that into my formula and actually got less of a results as my formula did... There should be like 40 items i only got 5.

Here is a link to what i its a small test before i get starting diving deeper in.

https://www.dropbox.com/s/1ewey84kb7rz2c2/TEST TEMPLATE.xlsx?dl=0