MAXIF with wild card

blueston

New Member
Joined
Nov 28, 2005
Messages
2
I have just recently learnt from this forum how to do a MAXIF function. But I have a twisted variation I would like to do. I will make up different data than what I am working on so it will be more interesting. So suppose I have the following-

Bob Smith 47
Mary Smith 32
Doug Smith 59
Mary Sullivan 19
Mike Jones 26
Katie Jones 22
John Doe 12
Jay Doe 16
Johnny Doe 8
Jill Doe 11

I want to determine the max age per family. So I know you can do MAX(IF(A2:A100="Bob Smith",B2:B100)) or the same except substiute a cell number (ex. E1 with the text Bob Smith), but is it possible to do MAX with a wildcard like MAX(IF(A2:A100="*Smith",B2:B100). When I try that and use CSE it returns zero.

Are there any Geniuses out there that can answer than one?

Thanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the board!

Try:

=MAX(IF(ISNUMBER(FIND("Smith",A1:A10)),B1:B10))

Entered with Ctrl + shift + enter.
 
Upvote 0
Is it possible to do this with multiple conditions to return the max age? Maybe there is a way to implement wildcards using the IFS function instead of IF. For instance if you want to search for the first and last name (lets assume there are people with the same first and last name... using it for something else)
 
Upvote 0
Have a look at the MAXIFS function.
Failing that I would suggest starting a new thread.
 
Upvote 0

Forum statistics

Threads
1,202,987
Messages
6,052,936
Members
444,616
Latest member
novit19089

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