Find partial text and retrun value in corresponding column
 

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

Find partial text and retrun value in corresponding column
 

hi,

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Find partial text and retrun value in corresponding column
 

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?

On Wednesday, December 26, 2012 1:57:49 PM UTC-5, wrote:
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

Find partial text and retrun value in corresponding column
 

On Wednesday, December 26, 2012 12:44:21 PM UTC-8, wrote:
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.