+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Dynamic VLOOKUP reference

  1. #1
    Registered User
    Join Date
    11-24-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    7

    Question Dynamic VLOOKUP reference

    I'm having trouble adapting this formula to my spreadsheet and I'm not sure I'm doing the right thing. I'd really appreciate some help.

    I have two tables stacked on top of each other; the number of entries in each table changes from month to month, which is why I need a dynamic vlookup.

    In Column A I have a bunch of titles. The first table is headed "Yes" (Cell A1) and the second table is headed "Possible" (for this month, Cell A42, but it will be a different row next month).

    In Column B I have a bunch of values (the number of times the titles were counted that month).

    As an example:

    A B
    Yes 10
    e 5
    d 4
    f 1
    Possible 12
    s 8
    e 3
    r 1

    I want the Vlookup to start its range right after the word Possible, to find a particular title from Column A, and to report the number of titles for that month (so the number in Column B). So for example, I'd be trying to find the value 3 if I were looking up title "e."

    I've modified a formula I found in an existing thread (here) to:

    =VLOOKUP(L10,INDEX(A:B,MATCH(L4,A:B,0)+1):L4,2,0)

    Where L10 holds name of the particular title I'm looking up (eg "e" from the example above; this title may or may not be in the first table), AB are obviously the columns contaning all my data, and L4 contains the word Possible (separately to column A becuase I can't identify the cell within Column A itself as it changes every month and I don't want people to have to search for it).

    I currently get a value of #N/A. :-(

    I am very new to this and clearly don't know what I'm doing! I'd really appreciate some help.

    [Also: very sorry that I posted this in the wrong thread earlier.]

  2. #2
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Dynamic VLOOKUP reference

    kindly upload a sample workbook with sample data and your desired results

    In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  3. #3
    Registered User
    Join Date
    11-24-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VLOOKUP reference

    Thank you for looking at my problem. The file is at work, and as it's 9pm here, I can't access it. I wouldn't be allowed to post it anyway, since it's all confidential data and what not. However, I've drawn up a rough example of what the real spreadsheet looks like (though there's a lot of other formulas in the real one.

    What I'd really love to be able to do is, using some variation of the formula I typed earlier, fill the cells I4:8 with the values from cells B14:18.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Dynamic VLOOKUP reference

    in F4 use
    =LARGE(OFFSET($A$1,MATCH("Yes",$A:$A,0),,MATCH("Possible",$A:$A,0)-MATCH("Yes",$A:$A,0)-1,2),ROWS(F$3:F3))

    and in I4 use

    =LARGE(OFFSET($A$1,MATCH("Possible",$A:$A,0),,MATCH(REPT("z",255),$A:$A)-MATCH("Possible",$A:$A,0),2),ROWS(I$3:I3))

    and copy down

  5. #5
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Dynamic VLOOKUP reference

    also try in F4 & I4 follwinng and copy down
    =LARGE(INDEX($A:$A,MATCH("Yes",$A:$A,0)+1):INDEX($B:$B,MATCH("Possible",$A:$A,0)-1),ROWS(F$3:F3))

    =LARGE(INDEX($A:$A,MATCH("Possible",$A:$A,0)+1):INDEX($B:$B,MATCH(REPT("z",255),$A:$A)),ROWS(I$3:I3))

  6. #6
    Registered User
    Join Date
    11-24-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VLOOKUP reference

    Hi Azam, and thanks for looking at my problem.

    However, I really need to use a dynamic LOOKUP formula. The spreadsheet I uploaded was a quick example of what I'm looking to do (due to the time difference, I was no longer at work). There are other formulas in the real spreadsheet, which I can't upload because it has confidential data in it.

    I've now created a better example spreadsheet!

    The summary tables that I'm trying to fill out need to be in order from largest to smallest for the current month. I've made clear in the sheet the areas that I'm trying to complete. I'd really appreciate some more help, and thank you very much in advance.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-25-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VLOOKUP reference

    Interesting, I never knew there was LARGE function. Endless this Excel.

  8. #8
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Dynamic VLOOKUP reference

    hi fuzzycells
    in your current file you do not pick the top ten previous or current values. you should mention the logic/criteria used to pick the values from the data

  9. #9
    Registered User
    Join Date
    11-24-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VLOOKUP reference

    Quote Originally Posted by Azam Ali View Post
    hi fuzzycells
    in your current file you do not pick the top ten previous or current values. you should mention the logic/criteria used to pick the values from the data
    Hi Azam,

    The top ten values are based on the highest values for the current month.

    For example, cells D3 to D12 are the highest values for the current month column for Yes values, they are in the summary table in I6:15. They are labelled as the yes table under the Current column.

    The summary table cells H6:15 are then entered as the values for the colours given in the previous month.

    For example, Red has the highest number in the Yes table for the current month (column D, with a value of 17). Therefore, it is listed first. The value for Red last month was 21 (from column B), so that goes into cell H6.

    That table works perfectly.

    What I'm trying to do is get the values from the Possible sections of the table, starting at cells AB 16 and cells CD18. They should be for the colours that got the highest numbers in the current month, that is, the ones listed in cells C 19:28.

    I'd really appreciate your help.

  10. #10
    Registered User
    Join Date
    11-24-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VLOOKUP reference

    *bump*

    I'd really appreciate some help.

    Can someone please give me a dynamic vlookup reference to start below a cell containing a particular word?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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