Max match

gorem

Board Regular
Joined
Sep 28, 2013
Messages
188
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
I think you can get what you want with just the match function.
Excel Workbook
ABC
1CatMax Row #5
2Dog
3Dog
4Dog
5Dog
Sheet
 

gorem

Board Regular
Joined
Sep 28, 2013
Messages
188
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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 omittedMATCH 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.
0MATCH 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

Board Regular
Joined
Sep 28, 2013
Messages
188

ADVERTISEMENT

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

Board Regular
Joined
Sep 28, 2013
Messages
188
FYI - what I have so far is: last_row = Evaluate("=Match(" & id & ",B:B,1)")
 

gorem

Board Regular
Joined
Sep 28, 2013
Messages
188
Nevermind - I got it to work. Was missing chr(34) in my string. Thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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
Top