# Max match

#### gorem

##### Board Regular
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!

### 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).
I think you can get what you want with just the match function.
Excel Workbook
ABC
1CatMax Row #5
2Dog
3Dog
4Dog
5Dog
Sheet

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!

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>

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.

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

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

Replies
3
Views
426
Replies
6
Views
142
Replies
11
Views
1K
Replies
2
Views
440
Replies
0
Views
258

Threads
1,202,967
Messages
6,052,850
Members
444,603
Latest member
dustinjmangum

### 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

### 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