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

**kmfdm515** · 08-30-2012, 05:31 PM

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

**Ace_XL** · 08-30-2012, 06:13 PM

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)

**kmfdm515** · 08-30-2012, 06:18 PM

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

**Ace_XL** · 08-30-2012, 06:22 PM

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.

**kmfdm515** · 08-31-2012, 02:41 PM

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?

**kmfdm515** · 09-03-2012, 07:25 PM

any ideas?

**kmfdm515** · 09-04-2012, 10:35 AM

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.