+ Reply to Thread
Results 1 to 7 of 7

vlookup range based on cell values / vlookup range start/end point?

  1. #1
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    vlookup range based on cell values / vlookup range start/end point?

    hello,

    i'm trying to figure out a way to make my vlookup range in a separate worksheet limited only to a portion of worksheet, based on values of certain cells in that same worksheet.

    i have attached an example of the data format i have, which i don't have the flexibility to change.

    using that example, i'd like to have the vlookup range limited to only the rows that are between "ORANGES" and "Total Oranges". The order of "Fruit" (in this example) is not locked.

    Is there an easy way to specify a dynamic vlookup range start/end point? I looked into dynamic lookups, but i did not find a way to do this. i searched through quite a few vlookup threads here, but didn't see anything covering this.

    example.xlsx
    Last edited by kmfdm515; 08-30-2012 at 05:45 PM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: vlookup range based on cell values / vlookup range start/end point?

    lets say your selected fruit is in D1. The selected size is in E1.

    Then use the formula
    =VLOOKUP(E1,OFFSET($A$1,MATCH(D1,A:A,0),,MATCH("Total "&D1,A:A,0),2),2,FALSE)
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: vlookup range based on cell values / vlookup range start/end point?

    edit: disregard, i saw your example. will give that a shot, thanks.

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: vlookup range based on cell values / vlookup range start/end point?

    In that case use

    Please Login or Register  to view this content.
    As you can see, the range from Oranges to ttal oranges is captured under
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: vlookup range based on cell values / vlookup range start/end point?

    I might be missing something here...but in the revised example you posted, if I delete row 20, the value returned changs to "2" instead of "N/A"...it's going down to the next section (Pears) I thought this OFFSET formula limited it to just the 'Oranges' section? Is there a way to change that, in case not all the data is identical in structure?

  6. #6
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: vlookup range based on cell values / vlookup range start/end point?

    any ideas?

  7. #7
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: vlookup range based on cell values / vlookup range start/end point?

    i'm a bit puzzled here - the exact opposite of what i expect to happen is happening.

    if i delete the row the the value i am looking for (4"), i would think it should return N/A, but it simply pulls the value from the next section down, which i obviously do not want.
    if i delete the row that says "ORANGES", i would think that the offset would start the range at the "Total Oranges" row, since it would in fact find the text 'Oranges' there. instead, it returns N/A in that case.

+ 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