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

  • Thread starter Thread starter Dan
  • Start date Start date
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"
 
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
 
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)
 
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
 
Assuming all ranges start in Row 1
doesn't this do what your formula does?
remove the beginning row adjustment
Click to expand...

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.
 
Back
Top