Search Column for value in Cell & Replace

[janglin]

I'm sure this is very easy to do, but I'm having a lot of troubl
figuring out how to do it

How do I search a column for the value in a particular cell, and the
insert the value into the current cell if found
<table
<tr
A B
<tr>1 jim james doesn't know what hes doin
<tr>2 james monkeys throw po
<tr>3 john jim is coo

</table
So I want to search all cells in column C for the non-case-sensitiv
value in B1. If the value in B1 is found somewhere in Column C, I wan
to insert that value into A1

Thanks in advance
Jame

 

[Max]

Assuming the range in col C is within C1:C20
Put in A1's formula bar, array-enter by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(B1="","",INDEX($C$1:$C$20,MATCH(1,--ISNUMBER(SEARCH(B1,$C$1:$C$20)),0)))
Copy A1 down as far as required

 

[janglin]

Thanks for your help. The above formula results in an #N/A error:

This is what the calculation steps showed:

=IF(FALSE,#N/A,INDEX(C:C,MATCH(1,--ISNUMBER(SEARCH("jim","james doesn'
know what hes doing")),0)))


The next evaluation will result in an error.

=IF(FALSE,#N/A,INDEX(C:C,MATCH(1,--ISNUMBER(#VALUE!),0))

 

[janglin]

Nevermind, I think it was because I wasn't properly entering the formul
as an array as you indicated I do before. Its working now. I don't thin
what I asked for earlier will actually accomplish what I really want t
do though.

I have a spread sheet filled with information about audio files an
their metadata. A lot of the cells in the title column contain th
artist in that file, but I want to seperate the artist from the title
into their own column. I have a list of possible artists that it coul
be. So I want to have a formula that will search the list of all th
possible artists, and return the match (if there is one) in that cell.


For Example:

____A__________________B______________________________C________
1_________KUT - Amos Lee - March 16, 20_______________del castillo
2_________EK - Billy Bragg - 3-18-06_____________________David Grisman
3_________Bluerunners on EK - March 09__________________Billy Brag
4_________Caroline Herring - 2-13________________________Amos Lee
5_________KUT - Casey McPerson - 2-21-06_______________Nirvana
6_________KUT - David Grisman - 2-04-06_________________Live
7_________KUT - 04-14-06 Del Castillo ____________________ZZ Top
8____________________________________________________Brent Palme
9____________________________________________________Alpha Rhythm


So I want to be able to extract the Amos Lee part out of the B1.Th
only way I can think of to do this is to search all of the B column fo
a match in the C column, and then place that matched value in the
column. Unfortunately the titles are not formatted in any particula
way, so its hard to extract it with delimiters or anything of tha
nature.


Thanks again for all your help

 

[Guest]

Same array formula as the earlier essentially, slightly tweaked to suit as
per your latest clarification ..

Here's an implemented sample:
http://cjoint.com/?itaEwlpkd3
janglin gen.xls

Array-Enter (press CTRL+SHIFT+ENTER) in A1, then copy down:
=IF(C1="","",INDEX($C$1:$C$9,MATCH(1,--ISNUMBER(SEARCH($C$1:$C$9,B1)),0)))
(see sheet X)

Longer but perhaps better with an additional error trap to return "blanks"
instead of #N/As, we can also place in A1, array-enter, then fill down:
=IF(C1="","",IF(ISNA(MATCH(1,--ISNUMBER(SEARCH($C$1:$C$9,B1)),0)),"",
INDEX($C$1:$C$9,MATCH(1,--ISNUMBER(SEARCH($C$1:$C$9,B1)),0))))
(see sheet Z)

Adapt the artists' range C1:C9 to suit ..

---

 

[Guest]

Fwiw, as a closure here for the archives' sake ...

Correction in this front part for the earlier 2 suggested array formulas:

=IF(C1="","", ...

should of course have read as:

=IF(B1="","", ...

Sorry for the gaffe

---