make match run backwards

[ryan00davis]

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

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| 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?action=getinfo&userid=37344
| View this thread: http://www.excelforum.com/showthread.php?threadid=570881
|

 

[JMB]

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]

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.

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[JMB]

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

 

[ryan00davis]

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 o
the first A1:A198. it looks like it makes sense to me, but doesnt wan
to work.

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

this is actually a piece of a code embeded in a longer equation whic
takes a bunch of dates and finds the lowest date, i need it to find th
lowest date between the cell it is starting at and the next time it say
client aged a/r above it. also i only wanted it to display for cell
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 t
pull the address part out of the entire equation.

thanks for the replys, maybe someone can tell me why the max equatio
isnt working?
rya

 

[daddylonglegs]

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+SHIF
keys whilst pressing ENTER. Curly braces should appear around th
formula in the formula bar and you should get a result.....

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

Try this formula

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

also confirmed with CTRL+SHIFT+ENTE

 

[ryan00davis]

both of those functions actually work, i think i might have been using
control enter instead of control shift enter.

thanks a lot, i have a working function so im no longer in any hurry,
but i dont really understand how that index function works.

im not sure why the function has 1/([array]="client aged a/r")

first i dont really get why this has 1 divided by the rest. is that
what makes it go backwards?

also if i look in the help, it says index returns the cell value or the
reference to the cell value, and control shift enter is what makes it
return an array, is this correct?

 

[JMB]

First, match can be used to find an exact match or an approximate match. In,
this case, daddylonglegs is using an approximate match. The match function
is used to return the index number of the largest value that is smaller than
or equal to the value being sought (2 in this case). However, the array has
to be sorted ascending (see help for match function requirements) which
precludes using [array]="client aged a/r" because that will return 1's and
0's and won't meet the ascending order requirement. 1/array="client aged a/r"
will return 1's and #DIV/0! which apparently will satisfy the sort
requirement.