+ Reply to Thread
Results 1 to 4 of 4

Nested vlookup function won't work

  1. #1
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Nested vlookup function won't work

    I am trying to use the SUM function to total a (horizontal) series of cells, but make the range itself variable. The range represents a series of monthly payments and I want to be able to show the year to date total as at a month that is input / selected by the user (for example the Jan to June running total if "June" is input)

    To do this the second argument in the SUM function needs to be variable. The idea is that this argument is the result of a nested vlookup function which shows the column (not the row) where the range to be summed ends.

    The vlookup function by itself
    =(VLOOKUP($C$3,Range,2,FALSE)&10)
    produces correctly in my case “F10” (C3 is where the user inputs the month in question). I don’t want to include the row number (10) in the vlookup table hence the hypersanded addition.

    The problem occurs when I enter this as the second argument in the sum function. Excel says that there is an error and won’t accept it.

    Can anyone tell me what the problem is (is it the hypersand?) and how to overcome it - with or without lateral thinking? Thanks.

    PS Last night my Excel program seems to have morphed into "Excel Starter". Whether or not this has got any bearing on the above I wish I could revert to what it was previously - a "non-starter" for a simpler life.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Nested vlookup function won't work

    Excel treats your F10 as a text string not a cell reference, to see it as a cell reference you need INDIRECT function, e.g.

    =INDIRECT(VLOOKUP($C$3,Range,2,FALSE)&10)

    You can use that to return either the value in F10 or the cell reference F10 within another function

    Personally, though, I would look at other ways to do this, possibly involving INDEX or OFFSET functions
    Audere est facere

  3. #3
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Nested vlookup function won't work

    Thanks, Thinking back to Lotus 1-2-3 days I suspected that there was an "indirect" way of achieving what I want to do (apart from me getting the ampersand / hypersand terminology right which was probably a contributory factor to the problem ..).
    I will try the INDIRECT function (to return the cell reference, not value) as you suggest. The other solutions might work also but just now I'm not sure how.

  4. #4
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Smile Re: Nested vlookup function won't work > except "indirectly"

    INDIRECT worked first time like a dream in this instance. Rarely if ever happens in my experience ..

+ 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: 7
    Last Post: 09-06-2009, 03:23 PM
  2. [SOLVED] Nested If with VLOOKUP's and AND function
    By Biff in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 05:05 PM
  3. Nested If with VLOOKUP's and AND function
    By Biff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 09:05 AM
  4. Nested If with VLOOKUP's and AND function
    By GBO in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. Nested If with VLOOKUP's and AND function
    By GBO in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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