# MAXIF with wild card

#### blueston

##### New Member
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.

Thanks. That worked. All hail the genius ... !

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)

Have a look at the MAXIFS function.
Failing that I would suggest starting a new thread.

Replies
6
Views
341
Replies
2
Views
157
Replies
3
Views
514
Replies
14
Views
1K
Replies
14
Views
1K

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.

### Which adblocker are you using?

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

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