Searching from bottom to top with MATCH?

  • Thread starter Thread starter Gunti
  • Start date Start date
G

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
 
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
 
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.
 
Gunti said:
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
Click to expand...


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))
 
Hey Jacob,

Thanks. I was looking for a formula that would give me the last value in a column with respect to some conditions(No TEXT & No '0').
With reference to formula I got how to get it done.
Thanks Again!
 
Back
Top