match/index 2nd/3rd/4th find

  • Thread starter Thread starter Thomas Robeerts
  • Start date Start date
T

Thomas Robeerts

Is there a function like match() and index() that will
give me the row # of all matches, not just the first.
thanks for the help.
 
One way

=INDEX($A$1:$A$20,SMALL(IF($B$2:$B$20=$H$4,ROW($B$2:$B$20)),ROW()-ROW($L$2)+
1))

entered with ctrl + shift & enter

note that index starts from the first row, that's because row will always
use the first row so you can either offset
this part

SMALL(IF($B$2:$B$20=$H$4,ROW($B$2:$B$20)),ROW()-ROW($L$2)+1)

with

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=$H$4,ROW($B$2:$B$20)),ROW()-ROW($L$2)+
1)-ROWS($L$2))

or do as I did use index from the first row

copy down until you get a num error or use an if function and avoid the
error but I believe errors are
good info when auditing formulas so I usually won't do that
 
Is there a function like match() and index() that will
give me the row # of all matches, not just the first.
thanks for the help.
Click to expand...

=SMALL(IF(Rng=X,ROW(Rng)),ROW(INDIRECT("1:"&COUNTIF(Rng,X))))-CELL("Row",Rng)+1

This returns a vertical array of all row indices in Rng matching X.
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following, array entered in a two column range of enough rows to
accommodate the output, will return an array of the row and column numbers:

Using the terminology of Harlan Grove in his response in this thread

=ArrayMatch(X,Rng)

If one were to use these functions, =ColumnVector(ArrayMatch(X, Rng),1)
would return the array of row numbers returned by Harlan Grove's use of
built-in functions [and =ColumnVector(ArrayMatch(X, Rng),2) would return
the array of the column numbers; and =ArrayMatch(X, Rng, "A") would
return the array of the worksheet addresses--an optional fourth argument
of 1 (the default),2,3, or 4 would return the addresses in row/column
absolute, row relative column absolute, row absolute/column relative,
row/column relative form, respectively].

Alan Beban
 
Hi Thomas
one way: use the following array formulas (CTRL+SHIFT+ENTER):
In B1 enter the following
=SMALL(IF($A$1:$A$99="lookup_text",ROW($A$1:$A$99),9999),ROW())
copy down

This formula will return either the nth row number or 9999.

HTH
Frank
 
Back
Top