MAtch Question

L

Lee

Is there a way to match a word in a cell with other words? Example A1 = Ford Probe, B1 = Ford Escort, C1 = VW Bug A4 match the word probe.
 
F

Frank Kabel

Hi
not really sure what you're trying to do. What is your expected result
in cell A4?

--
Regards
Frank Kabel
Frankfurt, Germany

Lee said:
Is there a way to match a word in a cell with other words? Example
Click to expand...
A1 = Ford Probe, B1 = Ford Escort, C1 = VW Bug A4 match the word probe.
 
L

Lee

What i would like to see is Ford Probe in cell A4. I need a way to match one word in a cell with multiple words in it.
 
F

Frank Kabel

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:C1,MATCH(TRUE,ISNUMBER(SEARCH("probe",A1:C1)),0))

This is NOT case sensitive. If you need a case sensitive formula
replace 'SEARCH' with 'FIND'

--
Regards
Frank Kabel
Frankfurt, Germany

Lee said:
What i would like to see is Ford Probe in cell A4. I need a way to
Click to expand...
match one word in a cell with multiple words in it.
 
L

Lee

I could not get the formula to work. In my program will have one or two words in the look up area A1 = Ford Probe B1 = Ford Escort C1 VW Bug D1 = Geo. What I would like to happen is A4 to = whats in B4 so if i hade Probe in B4 Then A4 = Ford Probe, Or if B4 = VW then A4 = Vw Bug or if B4 = Geo then A4 = Geo.
 
F

Frank Kabel

Hi
try
=INDEX(A1:C1,MATCH(TRUE,ISNUMBER(SEARCH(B4,A1:C1)),0))

entered with CTRL+SHIFT+ENTER as array formula

--
Regards
Frank Kabel
Frankfurt, Germany

Lee said:
I could not get the formula to work. In my program will have one or
Click to expand...
two words in the look up area A1 = Ford Probe B1 = Ford Escort C1 VW
Bug D1 = Geo. What I would like to happen is A4 to = whats in B4 so if
i hade Probe in B4 Then A4 = Ford Probe, Or if B4 = VW then A4 = Vw Bug
or if B4 = Geo then A4 = Geo.
 
Top