+ Reply to Thread
Results 1 to 7 of 7

Vlookup using MID to find values

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Vlookup using MID to find values

    Hi Girls and Guys,

    New member here coming to you cap-in-hand for advice.

    I have two workbooks that I need to make work together with the result being a semi-automated report. Workbook A is the one that I have built to compare values and is a .xlsx file (2010). Workbook 2 is a .xls ('97 clunker) that is the result of a PDF-to-Excel conversion from our internal reporting.

    What I need to do is Vlookup a number from my built .xlsx workbook and return a value taken from the .xls workbook.

    What I have is:

    Workbook A (2010) has a list of differing raw numbers listed in column 'A' (e.g. '12345' etc). I need to reference workbook B (antiquated 97) for those numbers and return the quantity listed against those numbers in that sheet on workbook A. Easy! Not so much. Workbook B has the number I need in its column A but due to the stupid conversion, it combines the number with a description (i.e. '12345 (description that CRFaig doesn't want)'

    So...I have tried to correct this by making an additional .xlsx workbook that does the '=A1'[jdjdjjjd.xls. etc]' and then adding a column B with a MID formula which returns the raw number and ignores the text within the brackets. My report file vlookup's from this workbook. It works but, seriously, I can grow half a beard waiting it to open on a decent-RAM'd PC.

    What I would like to know is, if it can be done, can a forula combining vlookup and MID functions make my built sheet return a value looked up on a sheet that has a number/then space/then bracket/then description/then closed bracket without needing a third conversion from a 'number with the stuff in the brackets removed' sheet?

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup using MID to find values

    Probably best if you upload the two workbooks so we can see the problem in context.

    Have you considered using a string function to split out the number in a helper column?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Vlookup using MID to find values

    Hi Richard,

    Thank you for the reply.
    Yes, will upload the workbooks in a few day's time. Yes, a helper column would work well but the issue is that I need to/want to leave the .xls file alone after it is generated and have my built workbook work with it so that a non-Excel-savvy person (someone else from work) can run the internal report, click a file and then view the results.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Vlookup using MID to find values

    Hi and welcome to the forum

    Maybe try a wild card in the criteria?

    =VLOOKUP("sector 8*",$B$2:$C$14,2,0)
    Remember that the numbers you are looking for (your sample had numbers), will actually be text, if they are included in a text string
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    03-05-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Vlookup using MID to find values

    Thanks for the replies.

    So this is what I have so far.

    I've uploaded the 3 files I currently have to use for this project here: https://www.dropbox.com/sh/s3im54bmgjnpl1n/xWNqO0WgKM

    Workbook A: The basis of the automated report function that I manipulate and build from.
    Workbook B: The .xls file that is built from a pdf-to-excel conversion out of a management system. I dont want to have to manipulate this file at all. Just run a report, save the report as 'Workbook B' and let the other workbooks refer to this.
    Process file: This is a helper workbook that replicates 'Workbook B.xls'; taking out the merged cells layout, addind a MID formula to column B to weed out the space, parentheses and contents within the parentheses.

    So what I'm trying to do: Tie in a Vlookup with a MID function in column B of 'Workbook A' so that it can reference 'Workbook B' without needing the Process file. As it currently stands, it works but, as mentioned earlier, it is so slow to load as Excel looks to calculate between three files on our network.

    What do you think? PS thank you also for the welcome to the forum. You've been very friendly thus far.
    Process File.xlsxWorkbook A.xlsxWorkbook B.xls

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup using MID to find values

    Hi,

    Not sure which column from B that you are wanting to pick up, but the formula below will pick up the total column K
    In B2 of workbook A copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-05-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Vlookup using MID to find values

    Hi Richard,

    Thank you very much. Yes, i was wanting to pick up from column E so I tweaked your awesome formula. It works a treat but with one little anomaly.

    An example is values of 202275 and 2275 in column A of workbook A return the same value when referencing to workbook B.

    What do you think?

+ 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. Vlookup to find one value in a cell that has multiple values
    By Riggs18 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-29-2013, 12:07 PM
  2. To find if vlookup matches multiple values
    By karthik11 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2013, 06:19 AM
  3. VLOOKUP to find values in text string???
    By newbieexceldude in forum Excel General
    Replies: 6
    Last Post: 12-18-2012, 01:21 PM
  4. find and add multiple vlookup values
    By MrJotun in forum Excel General
    Replies: 4
    Last Post: 08-06-2009, 03:25 AM
  5. How do I use VLOOKUP to find values across more than 1 sheet or Wo
    By RVFmal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2005, 03:05 PM

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