vlookup

Big Leeroy

New Member
Joined
Oct 14, 2006
Messages
6
I don't know if im using the right function for the job. I have gotten Vlookup to work but is it the right function to search through column a for a specific key word and return the value to D2.

Example:
Code:
         A            B               C                D
1  Key Words         Titles	Keyword Search        Result
2  Cat, Dog          Book 1         Tiger            Book 4
3  Snake, Fish       Book 2		
4  Drive, Race       Book 3		
5  Tiger, Dog        Book 4



If I enter the word Tiger in C2, then D2 would display the result Book 4.
Also is it possable to have it display multiple matches.

Example:
Code:
A                       B           C                     D
1  Key Words         Titles	Keyword Search           Result
2  Cat, Dog          Book 1         Dog            Book 2 Book 5
3  Snake, Fish       Book 2		
4  Drive, Race       Book 3		
5  Tiger, Dog        Book 4
If I enter the word Dog in C2, then D2 would display the result Book 2 Book 5.

Thanks for all your help.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
If you download and install the morefunc.xll add-in...

D2:

=SUBSTITUTE(MCONCAT(IF(ISNUMBER(SEARCH(C2,$A$2:$A$5)),","&$B$2:$B$5,"")),",","",1)

which you need to confirm with control+shift+enter, not just with enter.
 

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185
Alternatively, to display just the first instance:

=OFFSET(A1,MATCH("*"&C2&"*",A2:A5,0),1)

For multiple instances using a formula you may need to use the previous suggestion, but another way would be to use Advanced Filter in conjunction with a formula to display the relevant items in the list itself.

To do this, enter the formula

=SEARCH($C$2,A2)>0

in cell D2 and then use Advanced Filter with the criteria range = C1:C2.

Hope this helps.
 

Forum statistics

Threads
1,136,878
Messages
5,678,307
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top