using MATCH( match_type with text?

[Don88]

Hi All,


I haven't been able to find any definitive information on how the match_type argument works with text strings instead of values. it seems that if you use the "greater than" match type it will find the largest value, for example:


to match "apples" in an array of a1:a10 where each cell is "apples" it would return 10, or the highest value. changing a1:a9 to some other value does not affect it. is this an intended use of the MATCH( function or am I missing something?


Thanks


Don

 

[Montrey]

You can use Match with the Index function for what you're trying to do. Match will find the row# of a list of values in your case "10". You can then use =index function to find the value you want.


for instance if you use the index function on the same list of values you used the match function for, it will return the text string you are looking for, just put the cell# the match formula is in. Try it out!

 

[Montrey]

You can also use the match function for conditional formatting. You could make a list of words that you want to be highlighted if they occur in a certain list or dynamic range. Go to the conditional formatting menu and make a match function to the list of values. This will then apply the selected conditional format to any values of the list that appear in the dynamic list!

 

[Don88]

if I am not mistaken, using MATCH( on an array of unsorted text cells will only return the ordinal position of the FIRST match. is there some way to have it look for the position of the LAST match? EX: a list as follows

eggs

eggs

lemon

lemon

lemon

eggs


and have MATCH("lemon" return 5, for the LAST position that matches?

 

[SirJB7]

Hi, Don88!


MATCH retrieves the order of the first occurrence of searched value within the array.


Third parameter works like this:

1 or missing: finds the position of the greatest value that is less or equal to searched value (array should be in ascendent order)

0: finds the position of the first value that is equal to searched value (array in any order)

-1: finds the position of the lesser value that is greater or equal to searched value (array should be in descendent order)


You can access this from within Excel help features.


So...

a) 1 makes an approximation from low to searched value (found <= searched)

b) -1 makes an approximation from high to searched value (found >= searched)

c) as you can see from a) and b), it will always stop on the exact value or in the precedence in sort order: and that's why you can't retrieve the last position of a duplicate element within the array, it stops at the first.


Hope I aided to clarify.


Regards!

 

[Don88]

Thanks for the response, SirJB7.


That certainly helps my understanding of MATCH( , but I still don't understand of the third parameter works with non-numerical values. testing a simple list with the third parameter :1 SEEMS to return the last instance of a duplicated text element in an array, but as I try to use that with INDEX or OFFSET things stop working.


Is using MATCH,,1 with text an unsupported feature?


If so, is there a better way to return the last position in a text array of many duplicates?

 

[SirJB7]

Hi, Don88!

As far as I know and have tried, it works identically with numbers or strings, considering that for third parameter = 1 list must be sorted ascendent, for -1 descendent, and for 0 may be unsorted. For the rest, it's the same if you work digits or characters. And it always displays the position of the first element.

If you have an sample file to check out the return of last occurrence of repeated values, please upload it (check for further instructions at the first green sticky posts at this forums main page, if needed).

Regards!