Search for word in one column and write number in another...

This is my excel sheet:

(Col A) | (Col B)
OK-Q8 BANK AB | -1 984,00
Övf via internet | -15 200,00
MAXI ICA STORMAR* | -164,73

| = columnseparator

I want to search for the word ICA in col A and when it finds the word ICA it takes the number in col B and writes it in Col C.

Thanks for any help!

Also i want to search for other words but maybe take that later =P.

Try this:

=VLOOKUP("*ICA*",A:B,2,0)

though you might have to use semicolons instead of commas. You can put ICA in a cell somewhere (eg K1) and change the formula to this:

=VLOOKUP("*"&K1&"*",A:B,2,0)

Hope this helps.

Pete

Assuming your data starts on row 1:

C1:

=if(isnumber(search("ICA",A1)),B1,"")

And then copy downwards. It will fill in a value in C each time ICA appears and leave the rest blank.

Thanks guys! Really nice, this worked perfect =if(isnumber(search("ICA",A1)),B1,""). Any trick how to search for multiple words?

With a little OR magic

=if(or(isnumber(search("ICA",A1)),isnumber(search("daffodil",A1)),isnumber(search("hungryhungryhippos",A1)),B1,"")

If(or(condition1, condition2, condition3),B1,"")