• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Searching by keyword and autofilling neighboring column (vlookup?)

Laura

New Member
Hi,


I'm still pretty new to Excel, but I have a massive listing of people's names and titles. I need a way to search for keywords within their job titles in order to fill in a corresponding job role in the next column. Is this possible?


I tried using a =IF(ISERROR(SEARCH("*dentist*",C5,1)), "", "Dentist"), but I have a large list of job names and roles - it also cuts me off at 7 entries.


I then looked into arranging a vlookup with the hopes I could associate the vlookup formula to a corresponding table or sheet listing all the possible variations of that name corresponding to a role. Does this make sense?


For example:


Name Job Title Job Role Department

--------------------------------------------------------

Betty Super Dentist ? ?

Joe Prof. Clown

Tim Baby Doctor

Bobby Adult Baker


Corresponding table:


Word Role Department

--------------------------------------

Dentist Dentist Medical

Clown Clown Entertainment

Doctor Doctor Medical

Baker Baker Food


I would like to somehow search for a series of keywords in one column and then auto-fill the next column over with the correct role name and from that role the correct department.


Please let me know if I need to clarify. I have a ridiculous collection of titles to search through.


Thank you so much for your help!
 

Hui

Excel Ninja
Staff member
Laura

Have a look at using Index,Match combination

You will end up with sommething like =INDEX(Corresponding_Table_Range,MATCH(Job_Role,JobLst,0),Column_in_Table)


where

JobLst is the column of Jobs in the Corresponding Table

Column_in_Table is the column number from the Corresponding Table which has the data you want to return
 

kchiba

Active Member
Hi Laura,


In your first list record 1 = Name - Betty, Job Title - Super, Job Dentist and these are all in different columns?


The seocnd Lits record one - Word Dentist, Role Dentist, Department Medical


If this is the case you can use two VLOOKUP formulas, one to return the Role and one for the Department


Assume that the first list is in range A1:D5

and the second list is in A21:D25


the forula in E2 is =+VLOOKUP(D2,$B$22:$D$25,2,0)

and in F2 is =+VLOOKUP(D2,$B$22:$D$25,3,0)


Please let us knwo if this does not work
 
Top