i want to make match run from bottom to top, here is the formula im
working with:

(ADDRESS(MATCH("Client Aged A/R", A$1:$A198, 0), 10))
--------------------


right now it matches from A1 to A198 and returns the address of the first
time it sees "Client Aged A/R", i really want it return the last time it
sees "Client Aged A/R", which esentially would be the same thing as going
from a198 to a1 and returning the first.

thanks in advance
ryan


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

Sort the lookup table Descending and use -1 as 3rd argument
?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ryan00davis" wrote in message
...
|
| i want to make match run from bottom to top, here is the formula im
| working with:
|
| (ADDRESS(MATCH("Client Aged A/R", A$1:$A198, 0), 10))
| --------------------
|
|
| right now it matches from A1 to A198 and returns the address of the first
| time it sees "Client Aged A/R", i really want it return the last time it
| sees "Client Aged A/R", which esentially would be the same thing as going
| from a198 to a1 and returning the first.
|
| thanks in advance
| ryan
|
|
| --
| ryan00davis
| ------------------------------------------------------------------------
| ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
| View this thread: http://www.excelforum.com/showthread...hreadid=570881
|

im not sure what your saying, are you saying to use lookup instead of
match? if so how would i write the arguement:

i tried

=LOOKUP("Client Aged A/R", A$1:$A239, -1)

and it returned NA, if i changed the -1 to a1:a239 it returned client
aged a/r. if i changed it to b2:b239 just to see which cell it was
referencing it turned out that it was some random cell in the middle,
not the first one or the last one.

i also tried changing the match to -1 instead of 0, just in case thats
what you meant and that didnt work either.


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

I think Niek means just sort it descending, and use the ,-1 argument in
MATCH instead of ,0. But I think it should be 1 not -1.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ryan00davis"
wrote in message
...

im not sure what your saying, are you saying to use lookup instead of
match? if so how would i write the arguement:

i tried

=LOOKUP("Client Aged A/R", A$1:$A239, -1)

and it returned NA, if i changed the -1 to a1:a239 it returned client
aged a/r. if i changed it to b2:b239 just to see which cell it was
referencing it turned out that it was some random cell in the middle,
not the first one or the last one.

i also tried changing the match to -1 instead of 0, just in case thats
what you meant and that didnt work either.


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile:
http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

Another way that doesn't require sorting. Must confirm w/
Control+Shift+Enter. If done properly, excel will put braces { } around the
formula.

ADDRESS(MAX(IF(A1:A198="Client Aged A/R", ROW(A1:A1198),"")), 10)


"ryan00davis" wrote:


i want to make match run from bottom to top, here is the formula im
working with:

(ADDRESS(MATCH("Client Aged A/R", A$1:$A198, 0), 10))
--------------------


right now it matches from A1 to A198 and returns the address of the first
time it sees "Client Aged A/R", i really want it return the last time it
sees "Client Aged A/R", which esentially would be the same thing as going
from a198 to a1 and returning the first.

thanks in advance
ryan


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

small correction
ADDRESS(MAX(IF(A1:A198="Client Aged A/R", ROW(A1:A198),"")), 10)


"JMB" wrote:

Another way that doesn't require sorting. Must confirm w/
Control+Shift+Enter. If done properly, excel will put braces { } around the
formula.

ADDRESS(MAX(IF(A1:A198="Client Aged A/R", ROW(A1:A1198),"")), 10)


"ryan00davis" wrote:


i want to make match run from bottom to top, here is the formula im
working with:

(ADDRESS(MATCH("Client Aged A/R", A$1:$A198, 0), 10))
--------------------


right now it matches from A1 to A198 and returns the address of the first
time it sees "Client Aged A/R", i really want it return the last time it
sees "Client Aged A/R", which esentially would be the same thing as going
from a198 to a1 and returning the first.

thanks in advance
ryan


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

i tried

=ADDRESS(MAX(IF(A1:A198="Client Aged A/R", ROW(A1:A198),"")), 10)

and it returns #value. if i evaluate the formula it shows the error on
the first A1:A198. it looks like it makes sense to me, but doesnt want
to work.

sorting is not an option on this sheet, these are sheets coming out of
workbench that im making equations to pull data out without changing
the origional

this is actually a piece of a code embeded in a longer equation which
takes a bunch of dates and finds the lowest date, i need it to find the
lowest date between the cell it is starting at and the next time it says
client aged a/r above it. also i only wanted it to display for cells
that have a sum in column n.... here is the whole code:

=IF(N200="","",MIN(INDIRECT((ADDRESS(MATCH("Client Aged A/R",
A$1:$A198, 0), 10))):J200))

for testing and figureing out this part i figured it would be easier to
pull the address part out of the entire equation.

thanks for the replys, maybe someone can tell me why the max equation
isnt working?
ryan


--
ryan00davis
------------------------------------------------------------------------
ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344
View this thread: http://www.excelforum.com/showthread...hreadid=570881

Hi Ryan

=ADDRESS(MAX(IF(A1:A198="Client Aged A/R", ROW(A1:A198),"")), 10)

the above formula works if you confirm with CTRL+SHIFT+ENTER

To do this select cell with formula, press F2 then hold down CTRL+SHIFT
keys whilst pressing ENTER. Curly braces should appear around the
formula in the formula bar and you should get a result.....

...however, given your explanation of your wider aim I don't think you
really need ADDRESS or INDIRECT.

Try this formula

=IF(N200="","",MIN(INDEX(J1:J198,MATCH(2,1/(A1:A198="Client Aged
A/R"))):J200))

also confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570881