Thread: Allow Vlookup's range to start at the nth occurence of a text value

Is there a way to modify the range criteria within the Vlookup function to start at the n-th occurence of a text value?

Due to my industry, I am not allowed to use VBA.

What I am trying to do is have vlookup start when the text value "x" shows up for the second time in a column. For instance, there are two sets of data tables on one worksheet, the data sets are on top of each other and seperated by a common heading. The left most heading title is "Name". And, the tables depths are always changing. So a vlookup function that can start at the second occurance of "Name" will allow the function to work dynamically with the given data. The end range value is unimportant, because I can always put some random big number. Such as Ax:A1000, where "x" is the row number of the second occurence of "Name".

Any help on this would be great. Thanks a bunch.

You're awesome. Thanks NBVC.

Okay, so I've run into a little bug. The Index(Match) is turning into #VALUE! when Vlookup tries to use the outcome of Index(Match). I think the problem is that I'm pulling data from a different worksheet.

Ahhhhhhh, I didn't reference the worksheet in the closing range value :'Worksheet 2'!E25. I had :E25. Lol, you rock, thanks!

Hi,

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 your formula 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), 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 your help.

I'm not sure if you'll see this message as this thread is quite old, but I will cross my fingers anyway!