Where Is Chris Davison's MATCH Question?

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Chris,

Did you delete the question while I was busy composing a lengthy answer?

If so, Why?

:confused:

Aladin
This message was edited by Aladin Akyurek on 2002-03-17 16:14
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sorry Aladin, yes I did.

I realised what I was overlooking, so deleted it (obviously, not realising anyone was replying)

However, the conclusion I came to conflicts with my understanding of another syntax in another function, so I'm going to have to interrogate this further tonight after work, possibly reposting further.

Sorry to mess you around (and anyone else who was trying to reply to it)

cheers
Chris
:)
 
Upvote 0
drat - I need help afterall !

In the middle of setting up my first live dynamic range via :

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

(it'll be all text)

I'm confused about the -1 switch : does this mean I have to always have column A sorted descendingly?

Help file says :
"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on."

I refuse to believe it. I shouldn't have to go sorting all my info every time, should I ?

Aladin, please tell me it's a lot more complicated than that and that Dave's formula subtelly takes advantage of this with the "*" criteria....

many thanks
Chris
:)
 
Upvote 0
On 2002-03-19 12:17, Chris Davison wrote:
drat - I need help afterall !

In the middle of setting up my first live dynamic range via :

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

(it'll be all text)

I'm confused about the -1 switch : does this mean I have to always have column A sorted descendingly?

Help file says :
"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on."

I refuse to believe it. I shouldn't have to go sorting all my info every time, should I ?

Aladin, please tell me it's a lot more complicated than that and that Dave's formula subtelly takes advantage of this with the "*" criteria....

many thanks
Chris
:)

Yes, -1 is used only for arrays that are sorted in descending order. Furthermore, wildcards such as "*" can only be used with exact matches (match_type = 0).

Try using...

=OFFSET($A$1,,,MATCH(REPT("z",255),$A:$A))
This message was edited by Mark W. on 2002-03-19 12:57
 
Upvote 0
On 2002-03-19 12:17, Chris Davison wrote:
drat - I need help afterall !

In the middle of setting up my first live dynamic range via :

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

(it'll be all text)

I'm confused about the -1 switch : does this mean I have to always have column A sorted descendingly?

Help file says :
"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on."

I refuse to believe it. I shouldn't have to go sorting all my info every time, should I ?

Aladin, please tell me it's a lot more complicated than that and that Dave's formula subtelly takes advantage of this with the "*" criteria....

many thanks
Chris
:)

Chris,

It looks like you're saying that you succeeded to dynamically computed range by using

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

in A of "all text" type. Don't count on it too fast. What follows [which was meant to be a reply to your "deleted" query] evaluates the behavior of

MATCH("*",$A:$A,-1)

that you use within OFFSET. [ By the way, all this reminds me very much of the cautious thread on COUNTIF that Juan Pablo had initiated at the old board, which had ultimately the same goal: dynamically computing an alphanumeric range in a column. ]

Note: I'm copying verbatim my 'aborted reply' to your original query, which is very close to the current one.

=====================

I created in A1:A14 the following sample:

{"zax";"x";"w";"q";"d";"d";"c";"b";"aky";"a";"a";2;1;"b"}

[1]

=MATCH("*",A:A,1) results in #N/A.

[2]

=MATCH("*",A:A,0) results in 1.

[3]

=MATCH("*",A:A,-1) results in 14.

The sample above is unsorted.

The behavior of [1] is expected. I often exploit that behavior to determine the last row in a range filled up with duplicates:

1-Jan-02
1-Jan-02
1-Jan-02

within a dates range of say E2:E100 by

=MATCH("1-Jan-02"+0,E2:E100)

With the wild card (that is, "*") as lookup-value MATCH returns #N/A when it hits a blank cell in the range. [ This is a hypothesis. ]

The [2] returns 1 because * matches the first value in the range.

=MATCH("1-Jan-02"+0,E2:E100,0)

will indeed return the row number of the first 1-Jan-02 in E2:E100 in my date example.

The behavior of [3] is interesting in that it returns the row number of the last alpha valuenumeric value it finds in the range, even if the range is sorted ascending or descending or not at all. I think it is matching implicitly " " (a space char or an alpha char with the lowest ASCII code) to the alphanumeric values it comes across, failing to find one, it defaults to the row number of the last text value. If this could always be true, applying MATCH with match-type -1 would be useful. Alas.

Make " b" the last "b", it returns 11.
Make "!b" the last "b", it returns 11.
Now change "!b" to 2, it returns 11.

The result associated with the last three is correct. But, change the 2 (the latest change above) to sza, it still returns 11 instead of 14. At this point, MATCH as we apply it here, becomes unreliable. Maybe the change from alpha to numeric then back to alpha is the culprit in that the data type of the cell perhaps stays numeric, which MATCH with "*" rightly ignores.

Just experiment with " " alone in the first cell or second or third cell. All I get unreliable. I even got #N/A as result.

Recap. We don't still not have a reliable and efficient method of determining the row/column number of the last used cell in a column or row of the alphanumeric type as opposed to the numeric case.

This answers your:

"am I not able, therefore, to make a dynamic range name if the info isn't sorted in order ?"

if we paraphrase it as:

We can't use MATCH("*",A:A,-1) in order to create a (named) dynamic range regarding a column/row of alphanumeric type (that is, all text) as we can using MATCH(9.99999999999999E+307,A:A) where A is of numeric type.

Note. Often the volatile worksheet function COUNTA(A:A) is used, but that requires that no gaps in the data range in A occur.
==========================

Aladin
 
Upvote 0
On 2002-03-19 12:50, Mark W. wrote:

Yes, -1 is used only for arrays that are sorted in descending order. Furthermore, wildcards such as "*" can only be used with exact matches (match_type = 0).

Try using...

=OFFSET($A$1,,,MATCH(REPT("z",255),$A:$A))

MATCH(REPT("z",255),$A:$A))

appears to compute the position (row/column number) of the last cell of alphanumeric type.

Given

{255 z's;"z";"q";"d";"c";"b";"aky";"x";"a";"~";"";3;2;1}

in A1:A14,

where A1 houses =REPT("z",255),

=MATCH(REPT("z",255),A:A) [1]

returns 11.

When you also put

=REPT("z",255)

in A8, [1] returns 8!

I believe the foregoing is too farfetched. So I consider [1] practically usable.

Well done.

Aladin
 
Upvote 0
Thanks for your input too Mark, I can see how this is more reliable.

I originally concluded that the help syntax :

Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

could be read that the "z-a" was a catagory in itself : thus :

TRUE, FALSE, anytext,...2, 1, 0, -1, -2,..., and so on.

so *if* your column was just pure text, by definition, it was immaterial if it was sorted or not as there were no TRUES or numbers in the range to mess up the sort.

so far so good.

but, the smallest value that is greater than or equal to lookup_value of "*" didn't make sense. i couldn't see how this translated to the last row in the range.

Mistakenly assuming that it just *did*, I deleted the original post. Then however, I realised that this must also apply to VLOOKUP as the help syntax on TRUE is exactly the same :

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value.

I experimented with my John, Paul, George and Ringo range (all pure text unsorted) and got incorrect vlookup values, so my above logic was unsound, hence this post.

Dave, I was using the original offset formula from your very helpful dynamic range names example page, having recognised the potential of your excellent D functions examples. Can you add to this at all ?

many thanks
Chris
:)
 
Upvote 0
Chris, be advised that spaces (" "), null text strings (""), and some special characters (e.g., "-", ")", "&") wreak havoc on =MATCH("*",A:A,-1), but have no impact on =MATCH(REPT("z",255),A:A). Also, if A:A are text values of known or fixed length (i.e., account numbers, zip codes) you can reduce the number of REPT'd z's to only exceed that limit by 1 and; thus, improve efficiency.
This message was edited by Mark W. on 2002-03-19 15:27
 
Upvote 0
I hear you Mark !

your formula appears to be more concise and reliable, I'll be applying it tommorrow. That original MATCH function within the OFFSET just threw me totally when I interrogated it.

thanks again
:)



edit..... nice intuition too : my ranges will be columns of fixed-length text Tax codes, fixed-length text Tax systems,fixed-length text account codes etc etc... normal accounting system database output
This message was edited by Chris Davison on 2002-03-19 15:55
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top