Max match

[gorem]

Hi all,

I need to return the largest row number in a column which contains a certain value. I am thinking I can use a combination of Max and Match as an array formula, but I'm a little stuck. Imagine the following five values are in column A and I want to return the greatest row number containing "Dog":

Cat
Dog
Dog
Dog
Dog

I would expect the formula to return 5. Any ideas? Thanks!

 

[AhoyNC]

I think you can get what you want with just the match function.

Excel Workbook
	A	B	C
1	Cat	Max Row #	5
2	Dog
3	Dog
4	Dog
5	Dog
Sheet

 

[gorem]

That worked great! Question - I only use Match as part of Index/Match, and I always use 0 as the last term to find exact matches. What exactly does the 1 do? Thanks!

 

[AhoyNC]

The 0 looks for an exact match and returns the first one it finds. As explained in the help below 1 returns the last value that matches (in this case). So, in your case 0 would return 2.


From Help

1 or omitted	MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0	MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

<tbody>
</tbody>

 

[gorem]

Amazing - thanks so much!

This is enough to get me started. If you have a moment, I'd love to know how to use that formula in VBA with Evaluate, and a variable in place of the actual string "Dog". For instance, if the variable "id" contains the string I'm looking for. I keep getting a type mismatch error.

 

[gorem]

FYI - what I have so far is: last_row = Evaluate("=Match(" & id & ",B:B,1)")

 

[gorem]

Nevermind - I got it to work. Was missing chr(34) in my string. Thank you again!