Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Vlookup where value is a formula

    Hello Everyone-I have MS Excel 10 and need to do a vlookup function. The vlooup function is looking up a cell that has the following formula in it "="1"&mid(5,3) in A2. When I do the vlookup function (=vlookup(a2,budgets,3) I get an #N/A error. When I look into the calculation steps, it does the formula for the MID step first and returns an error on the Vlookup calcuation. Can someone help me find out what I am doing wrong? Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    First off, what cell is your MID function referencing? You wrote: MID(5,3), but you need a column letter before the 5 and then the number of characters if you're starting a character 3. For ex., MID(A5,3,2) finds the 2 characters starting at the 3rd in cell A5.

    Also, if your data in the first column of budgets is not sorted, you'll need FALSE after the 3, in the VLOOKUP.
    Last edited by kweaver; 2013-04-04 at 15:56.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry-MID(B2,5,3). the Budget in the vlookup is a label to a range coming from another worksheet in the same spreadsheet

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Try: =vlookup(A2,budgets,3,false)

    The #N/A would suggest that A2 isn't found in the first column of budgets.

    So, maybe: =IFERROR(vlookup(A2,budgets,3,false),"Not Found")
    Last edited by kweaver; 2013-04-04 at 16:03.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks that works.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Unless I know for certain that all values should be found, I wrap most of my VLOOKUP functions in ISNA functions. ISNA and ISERROR are similar, but ISNA is tuned to the specific error returned when a lookup doesn't match anything in the array being searched.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    True but:
    1. The lookup might work and the return cell actually contain the #N/A error
    2. IFERROR is much more efficient (if you have 2007 or later) because it only does the VLOOKUP once.
    3. INDEX and MATCH are more efficient than VLOOKUP (and allow you to cater for issue 1)

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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