+ Reply to Thread
Results 1 to 4 of 4

Extracting a string and using it as the input for a VLOOKUP on a different sheet.

  1. #1
    Registered User
    Join Date
    07-05-2019
    Location
    Washington State, USA
    MS-Off Ver
    365
    Posts
    2

    Extracting a string and using it as the input for a VLOOKUP on a different sheet.

    Hello,

    I'll throw this out to see if anyone has seen this. I didn't find anything like it when searching the forum. Thanks in advance.

    I'm trying to extract a value from a string in a cell and use it as the basis for a VLOOKUP on a different sheet.

    For example:
    Cell D63 on Sheet 1 contains "64500 - 54488 - Custom work"
    Sheet2 A2 thru B300 contains the lookup table of custom projects and project owners
    I need to be able to extract the "54488" from the string in D63 and use it as the lookup from Sheet2 and return the corresponding custom project owner.

    I've been able to extract the "54488" using =SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","")

    I've been able to use =IFERROR(VLOOKUP(0+LEFT(D60,FIND(" ",D60,1)-1),Sheet2!$A$2:$B$300,2,FALSE),"") to lookup the project owner when the project number, 54488, is the first value in the string. But my input data is changing and the 54488 is no preceded and followed by " - ".....space dash space.

    When I tried using SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","") as the first parameter for the VLOOKUP, I get #N/A. I've confirmed that 54488 is in the table with a corresponding project owner.

    Any ideas?

  2. #2
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    221

    Re: Extracting a string and using it as the input for a VLOOKUP on a different sheet.

    sample excel spreadsheet with example

  3. #3
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    221

    Re: Extracting a string and using it as the input for a VLOOKUP on a different sheet.

    FYI, all character got by using "Substitute" is TEXT format (not numerical value), you could do "trouble shooting" as follow

    Try changing the first parameter of vlookup to following (if "n/a" not shown, that means all value in column A of sheet 2 is numerical value)
    SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","")-0

    Text value (54488) from D63 of sheet 1 could also be obtained by (all character except space between 2 dash in D63)
    =TRIM(MID(SUBSTITUTE(D63,"-",REPT(" ",500)),500,500))

  4. #4
    Registered User
    Join Date
    07-05-2019
    Location
    Washington State, USA
    MS-Off Ver
    365
    Posts
    2

    Re: Extracting a string and using it as the input for a VLOOKUP on a different sheet.

    Thanks. I'll play around with that over the weekend.

+ 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. [SOLVED] Length of string, not value related to string appearing in input box prompt.
    By ColdDay in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-23-2014, 05:32 PM
  2. Replies: 2
    Last Post: 05-09-2014, 09:56 AM
  3. Extracting data from one sheet to another using offset & multiple vlookup
    By Navin Agrawal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2013, 12:40 AM
  4. Replies: 12
    Last Post: 03-20-2013, 05:46 PM
  5. Search for any string / text greater than AND for string / text less than input box
    By JaneinExcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2013, 04:18 PM
  6. [SOLVED] how to multiple vlookup from input sheet to output sheet
    By nur2544 in forum Excel General
    Replies: 7
    Last Post: 11-21-2012, 01:48 AM
  7. How to use sheet name in Vlookup using user input
    By dagonette in forum Excel General
    Replies: 2
    Last Post: 11-04-2012, 10:43 AM

Tags for this Thread

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