Indexing an Array with VLOOKUP

[Joe Blow]

Hi,

I have an array with multiple occurrences of my lookup value that I
would like to summarize in another table.

Is there a way to index to the next occurrence of the lookup value
once vlookup has found the first occurrence?

Appreciate any help,
Joe

 

[Jason Morin]

One way:

=INDEX(B1:B10,SMALL(IF(A1:A10="X",ROW(B1:B10)),N))

Array-entered (press ctrl + shift + enter), where:

B1:B10 = range that contains value to return
A1:A10 = range to search for lookupvalue
X = lookup value
N = positive integer representing nth occurence (e.g., 2
= 2nd occurence, if there is one)

HTH
Jason
Atlanta, GA