• 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.

VLookup Specific word

D

Danny

Member
Hi people

I have a list of departments to which in pulling from one work sheet and displaying on another using a Vlookup.

I wanted to know whether there is an easy way for filtering the words it displays. In the example below I want the department details to be pulled from work sheet 2 and displayed on worksheet one without the word Europe in. Is this possible? (I want to keep the first and third word only).

Thank you in advance
 

Attachments

  • Vlookup.xlsx
    11.1 KB · Views: 14
Hi Danny,
Is this useful?
=REPLACE(VLOOKUP("*Europe *",$N$6:$N$10,1,0),FIND(" ",VLOOKUP("*Europe *",$N$6:$N$10,1,0),1),7,"")

But I am afraid that the lookup will only pull the first value that matches to it...In your sample file there are various values that have the word "Europe"
 
Abhijeet, thank you for your reply.

I will give that a try, looking at the real data I have i can see that there are only ever two possibilities at the start. Its either CC Europe *department* or BA Europe *department*

If I only have two static first values does this change anything?
 
Hi Danny ,

You can use the standard approach to retrieve multiple occurrences ; use this formula in the Name column , and copy it across and down to the Age and Department columns :

=SUBSTITUTE(INDEX(L$6:L$10,SMALL(IF(SEARCH("Europe",$N$6:$N$10)+0,ROW($N$6:$N$10)-MIN(ROW($N$6:$N$10))+1),ROW(A1))),"Europe","")

Enter the above as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Hi @NARAYANK991 : Can you help me understand the use of the 'small' & 'Min' formula in your CSE formula?

I have seen a lot of people use it, may I could never understand it and now feeling an urge to learn it..:)
 
You must log in or register to reply here.
Back
Top