Hello all,
Let me describe my sheet:
In column A there are part numbers, in column B there are locations. If you type a part number in cell D5 an array function in column E will search for all matches of that part number and give you all the locations.
Now the problem:
The SEARCH function gives you a TRUE even if the text you are matching is in the middle of the string. So if I type in "12 UNF" into cell D5 I get all the part numbers locations for that in column E, great, BUT! I am also getting the parts "10-12 UNF" because that is how the SEARCH functions searches. I don't want the 10-12 just start searching from 12 UNF and beyond.
Current Equation:
=IF(ISERROR(INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH($D$5,A:B,1)),ROW(A:A)),ROW(2:2)),2)),"",INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH($D$5,A:A,1)),ROW(A:A)),ROW(2:2)),2))
I have had no luck with getting the match function (MATCH($D$5&"*",A:A,0)) to give me a proper TRUE/FALSE array to use my SMALL function with. Perhaps this is the wrong path.
Any help is welcome and I hope someone can give me some guidance.
Thank You,
CABE
Let me describe my sheet:
In column A there are part numbers, in column B there are locations. If you type a part number in cell D5 an array function in column E will search for all matches of that part number and give you all the locations.
Now the problem:
The SEARCH function gives you a TRUE even if the text you are matching is in the middle of the string. So if I type in "12 UNF" into cell D5 I get all the part numbers locations for that in column E, great, BUT! I am also getting the parts "10-12 UNF" because that is how the SEARCH functions searches. I don't want the 10-12 just start searching from 12 UNF and beyond.
Current Equation:
=IF(ISERROR(INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH($D$5,A:B,1)),ROW(A:A)),ROW(2:2)),2)),"",INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH($D$5,A:A,1)),ROW(A:A)),ROW(2:2)),2))
I have had no luck with getting the match function (MATCH($D$5&"*",A:A,0)) to give me a proper TRUE/FALSE array to use my SMALL function with. Perhaps this is the wrong path.
Any help is welcome and I hope someone can give me some guidance.
Thank You,
CABE