formula issues

[Guest]

I could not get the formula =INDEX(A1:C1,MATCH(TRUE,ISNUMBER(SEARCH("probe",A1:C1)),0)
to work. In my program will have one or two words per cell in the look up area example 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.

 

[Guest]

Sorry, I'm missing something.... why not just
have in A4 cell '=B4' ???

-----Original Message-----
I could not get the formula =INDEX(A1:C1,MATCH (TRUE,ISNUMBER(SEARCH("probe",A1:C1)),0))
to work. In my program will have one or two words per

cell in the look up area example 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.

 

[Guest]

because i need to puul it from the range of cells a1 - d1

 

[Peo Sjoblom]

Enter the formula with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Guest]

Thankyou that helps alot. But why does ctrl + shift & enter have to be used to make it work?

 

[Peo Sjoblom]

Because it is an array formula, for some info see::

http://www.cpearson.com/excel/array.htm

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Thankyou that helps alot. But why does ctrl + shift & enter have to be

used to make it work?

 

[Gord Dibben]

lee

See Chip Pearson's site for Array formulas and explanations of.

http://www.cpearson.com/excel/array.htm

Gord Dibben Excel MVP

 

[daniels012]

1st
Have a table to look up values
A B
1 Bug VW Bug
2 Escort Ford Escort
3 Geo Geo
4 Probe Ford Probe

Put this formula in cell A5
=VLOOKUP(B6,$A$1:$B$4,2)
5 Ford Probe Probe
6 Geo Geo
7 Ford Escort Escort

Then copy the formula into cell A6, A7 etc.

I tried it and it worked fine!!

Hope this helps!
Michae