Searching from bottom to top with MATCH?

[Gunti]

Hi,

I'm using MATCH to find a value in an array. B2:B35000

However, there are multiple matches. Right now it takes the first value it
finds (from B2 to B35000). Can i tell excel to get the latest MATCH? (Search
from 35000 to B2).

gr,
Gunti

 

[Jacob Skaria]

The below array formula will return the row number of the last MATCH..

=MAX(IF(B2:B35000="findtext",ROW(B2:B35000)))

If this post helps click Yes

 

[Rick Rothstein]

As a follow up to Jacob's post...

If you are unfamiliar with array formulas, they are entered differently than
regular formulas. Your cursor must be in the Formula Bar and you must hit
Ctrl+Shift+Enter to commit them, NOT just the enter key by itself. Excel
will place curly braces {} around the formula to indicate its an array
formula. You cannot put the curly braces in yourself... Excel must do it in
response to the Ctrl+Shift+Enter sequence for the formula to work correctly.
If you modify the formula at any time in the future, you must recommit the
formula using the Ctrl+Shift+Enter sequence in order to get Excel to
recognize it as an array formula again.

 

[Domenic]

Hi,

I'm using MATCH to find a value in an array. B2:B35000

However, there are multiple matches. Right now it takes the first value it
finds (from B2 to B35000). Can i tell excel to get the latest MATCH? (Search
from 35000 to B2).

gr,
Gunti

To return the position, relative to B2, try...

=LOOKUP(2,1/(B2:B35000=A2),ROW(B2:B3500)-ROW(B2)+1)

To return the native position, try...

=LOOKUP(2,1/(B2:B35000=A2),ROW(B2:B3500))