Find partial text and retrun value in corresponding column

[dannich123]

Hi All

I have an inventory spreadsheet that has an A1 column approx 5000 rows long, that has part descriptions and a B1 column of part locations Approx 5000 rows long.

I want to be able to enter a search criteria in a 3rd column say c1 and be able to look for partial matches in the description column.
For Example

if the Description says "Guide Roller shaft" I would like to be able to display the part location in the column next to it if I were to enter "Guide" or "Roller" or "Shaft" in the search criteria box

SO if the columns were as follows and I entered "Roller" or "Guide" or "Shaft" in to C1 I would see A178 in D1 my results column.
Also there may be several instances of the search criteria found within the description column. I would like to be able to see all locations returned in a list below the D1 column heading.

A1 b1 C1 D1
Guide Roller Shaft A178 Roller A178

 

[Claus Busch]

hi,

Am Wed, 26 Dec 2012 10:57:49 -0800 (PST) schrieb (e-mail address removed):

A1 b1 C1 D1
Guide Roller Shaft A178 Roller A178

try in D1:
=INDEX($B$1:$B$100,MATCH("*"&C1&"*",$A$1:$A$100,0))


Regards
Claus Busch

 

[dannich123]

YEs that works great but it only lists the first instance offound of the search criteria. Can you show me how to make it find and list all that it finds?

 

[MyVeryOwnSelf]

YEs that works great but it only lists the first instance
offound of the search criteria. Can you show me how to make it
find and list all that it finds?

Here's one way, using E as a helper column.

In E1 put
=IF(ISERROR(FIND($C$1,A1)),"",1)

In E2 put
=IF(ISERROR(FIND($C$1,A2)),"",MAX($E$1:E1)+1)
and copy down past the end of the list.

In D1 put
=IF(ROW()>MAX(E:E),"",INDEX(A:B,MATCH(ROW(),E:E,0),2))
and copy down past the end of the list.

Hope this helps getting started.