second or third match in vlookup() or Match()

[Dan]

Is it possible to get the second or third match instead of the first?
Thank you

 

[Gary''s Student]

Say in A1 thru A100 we have:

now
is
the
time
for
all
good
men
to
sidney
or
some
other
place
other
than
sidney

=MATCH("sidney",A1:A100,0) will find the first sidney


Clearly if the first sidney is in cell A10, then to find the "next" sidney
we would like to use:


=MATCH("sidney",A11:A100,0)+10 but automate the process.


So if D1 contains:


=MATCH("sidney",A1:A100,0)
then in D2 enter:
=MATCH("sidney",INDIRECT("A" & D1+1 & ":A100"),0)+D1
and copy down. This will give you the row numbers of all the "sidneys"

 

[Rick Rothstein \(MVP - VB\)]

Assuming your data starts in Row 2 (with Row 1 being a header row), and that
the text you want to find is in B2 and the instance number of the text you
want to find is in C2, this array-entered** formula will return the ROW
NUMBER of that instance of the text...

=SMALL(IF(A2:A1000<>B2,"",(A2:A1000=B2)*ROW(A2:A1000)),C2)

** Array-entered means commit the formula using Ctrl+Shift+Enter, not just
Enter by itself.

Rick

 

[T. Valko]

Try this array formula** :

D1 = lookup value

=INDEX(B1:B10,SMALL(IF(A1:A10=D1,ROW(B1:B10)-MIN(ROW(B1:B10))+1),n))

Where n = the instance number you want to find.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Rick Rothstein \(MVP - VB\)]

Assuming all ranges start in Row 1, doesn't this do what your formula does?

=INDEX(B1:B10,SMALL(IF(A1:A10=D1,ROW(B1:B10)),n))

All I did was remove the beginning row adjustment from you formula, namely
this part...

-MIN(ROW(B1:B10))+1

Or were you assuming your formula would be copied down (I didn't get that as
a requirement from the OP's posting).

Rick

 

[T. Valko]

Assuming all ranges start in Row 1

doesn't this do what your formula does?
remove the beginning row adjustment

Yes, but....

99 out of 100 people that use this type of formula don't understand what
ROW(...) is doing so using:

ROW(B1:B10)-MIN(ROW(B1:B10))+1

Is the most fool-proof method to get things to work properly. As an added
bonus, this expression also makes the formula robust against row insertions.

Some folks use: ROW(INDIRECT("1:"&ROWS(rng)))

The formula is already not very efficient on large data sets so adding
volatility to the mix just makes things worse.