Search / Vlookup

Hi. Im just new here and not really that good in excel software. Just wanna ask if is it possible to combine SEARCH function and VLOOKUP function. Because i wanted to search for a particular name: example

in column A

excel programming
how to use excel
basic excel guides
basic codes for ..........
codes for ..........
programming codes........

in column B

here i will make a querry for column B from column A where i will search for a string like "EXCEL" w/c will return a value like "HI!" and if im looking for a word "CODES" it will return "HELLO"...

in short i have a lot of criteria to be searched and that i will use SEARCH function (because it is not case sensitive unlike VLOOKUP that has limited function)..

Hoping for you great help.
thanks

You could try using a nested if statement

Please Login or Register  to view this content.

Where if the string to search for is found a number will be returned, the position is found at.
Hence ISNUMBER(SEARCH("Excel",A2)) = TRUE if found =FALSE if not.

Hope this helps.

thanks for the reply marcol but unfortunately im not just searching for 2 strings.. how about if im querrying for more than 15 strings/criteria?
im using excel 2003 and has limited formula. it can only cope up to 6 function.
thats why im asking if it is possible to combine vlookup and search function.

example:

In column a


excel programming
how to use excel
basic excel guides
basic codes for ..........
codes for ..........
programming codes........

In Column B

excel
codes ------------------ this would be the value to be matched and SEARCHed and to be returned it column C..

---the process would be like this ( search strings in B in column A; and if it has it. Column C value = B---------

is it possible? sorry for making this hard ^_^
coz im just a noobs

Hi weakpoako

look the attatched file for formula

it is an array formula and should be confirmed thourgh Ctrl+shift+Enter

If you just want to know if any of the search words are in the string.
Put the search words in say C2:C15 (no blanks)

Then in Column B this array formula

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter not just Enter.
Drag/Fill Down.

If you need to identify each string as found with a unique value then that's a different matter. (Probably using two or more nested IFs in seperate columns would be easiest.)

[Edit]
Try this workbook.

If more than one search word is found in the string, what should the returned value be?

Hope this helps.

Thanks both of you.. It works..
thats what i really wanted to do..
^_^

i forgot that it is an array and needed to be ctrl+shift+enter
god bless and more powers

now i solved the riddle in what i am trying to do..
a job well done