+ Reply to Thread
Results 1 to 8 of 8

Variabel text in MID. VLOOKUP?

  1. #1
    Registered User
    Join Date
    06-21-2017
    Location
    Sweden
    MS-Off Ver
    Professional Plus 2010
    Posts
    5

    Variabel text in MID. VLOOKUP?

    Hi,

    Iīm trying to do the following:
    I write a name in A1. The formula searches in C3-E4 to find that name. If it finds it it returns the initials between the paranthesis.

    I canīt, for different reasons, separate name and initials in different cells and just use VLOOKUP. That is, I canīt change the way the document looks. (The image is just a simplified version of the big document who we send out externally).

    This far I have just used:
    =MID(C3; SEARCH("(";C3)+1; SEARCH(")"; C3)-SEARCH("("; C3)-1)

    Above doesnīt work since I want to be able to change name in A1 and therefor get different initials in B1. I tried exchanging C3 with VLOOKUP(A1; C3:E4; 1; TRUE) but it doesnt gave me a correct value back. (and probably is completely wrong but Iīm kind of stuck here..)

    So question is: What formula can I use to be able to do this?

    Many thanks!
    //Erik

    excel.JPG

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Variabel text in MID. VLOOKUP?

    welcome to the forum, Erik. try this array formula:
    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    You might need to change my commas to semi-colons. or simply refer to the file
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    06-21-2017
    Location
    Sweden
    MS-Off Ver
    Professional Plus 2010
    Posts
    5

    Re: Variabel text in MID. VLOOKUP?

    Thank you for a swift reply!

    I tried to alter your formula a bit to suit my document. The cell where I change the name is H66 och and the search area is H80:Q87.
    One thing to notice is that every second column between H and Q are empty due to design reasons in the document. Donīt know if it matters.

    Unfortunately I get an error when inserting the below formula.. Donīt know whats wrong. Do you see it?

    =MID(INDEX($H$80:$Q$87;1;MIN(IF(LEFT($H$80:$Q$87;FIND("(";$H$80:$Q$87)-2)=H66;COLUMN($H$80:$Q$87)-COLUMN($H$80)+1;99^99)));FIND("(";INDEX($H$80:$Q$87;1;MIN(IF(LEFT($H$80:$Q$87;FIND("(";$H$80:$Q$87)-2)=H66;COLUMN($H$80:$Q$87)-COLUMN($H$80)+1;99^99))))+1;2)

    Best Regards
    Erik

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Variabel text in MID. VLOOKUP?

    i'm sorry. forgot to change a portion of it. it should be:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-21-2017
    Location
    Sweden
    MS-Off Ver
    Professional Plus 2010
    Posts
    5

    Re: Variabel text in MID. VLOOKUP?

    Still an error Iīm afraid. Donīt know if I can share the error code with you somehow?

    Many Thanks

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Variabel text in MID. VLOOKUP?

    Hi all- Does this ARRAY FORMULA work? It requires Name on the left end and (initials) on the right end of the found string = James...(JE)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    here's the same formula with semi-colons instead of commas:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    *You must press CTRL+SHIFT+ENTER to confirm entry of an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.


    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-21-2017 at 12:50 PM.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2402
    Posts
    13,390

    Re: Variabel text in MID. VLOOKUP?

    I am not sure where to swap ";" and "," for regional settings. This formula is the American version. The file is attached. Hopefully your settings will correct.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  8. #8
    Registered User
    Join Date
    06-21-2017
    Location
    Sweden
    MS-Off Ver
    Professional Plus 2010
    Posts
    5

    Re: Variabel text in MID. VLOOKUP?

    @leelnich:
    Your formula worked (almost). One problem is that when I enter the following:
    Enter: CompanyX

    Then the formula search the area with all the company names - find "CompanyX (BBB/K2)" end returns just "K2".. I want it to return "BBB/K2".. The length between the parenthesis varies.. It can be "NR", "BBB/A-2", (AAA/K1) and so on..

    Thank you guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  2. Variabel data range of formulas and charts
    By mark02 in forum Excel General
    Replies: 1
    Last Post: 12-13-2012, 10:04 PM
  3. how to change criteria Range become variabel in advanceFilter
    By venol in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-18-2011, 11:40 AM
  4. Using a variabel columnIndex in Vlookup function
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2009, 03:34 AM
  5. How to replace a filename by a variabel
    By Dibo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2006, 06:27 PM
  6. creating a VBA variabel from multiple named worksheet ranges
    By brachistochrone in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2006, 05:20 AM
  7. getting the sum of variabel ranges
    By Hein in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2006, 02:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1