### About this tutorial:

**Video duration: **10:29

Download file:

Rotate Table:

1. Type “man” and return all e-mails and phone numbers with last names that start with “man”

2. See the new 2010 Function AGGREGATE

3. See the functions SEARCH, INDEX, ROW, ROWS, IF and ISNUMBER

Related videos:

Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records

Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records

## 19 thoughts on “Excel 2010 Magic Trick 798: Partial Text Lookup Formula To Return Multiple E-mail Records”

## Denis Allen

(January 8, 2019 - 3:56 pm)Thanks ExcellsFun for the great trick. This works 99.9% perfect for me except the formula returns zeros if there is no data in a field. It would be great if it could be left empty or blank. I'm using Excel 2016. Can you help me out please.

## vimalraj rasathurai

(January 8, 2019 - 3:56 pm)Hey i have a question!

If we delete search text all the data comes. How to hide??

## Lily Suarez

(January 8, 2019 - 3:56 pm)Such a great tutorial! Thanks SO MUCH!

## Rankle Rave

(January 8, 2019 - 3:56 pm)Can someone tell me if their is a function I can use that is similar to the Find & Select (binoculars) feature? I need to place a "Search for Client" box in the upper left hand corner of my spreadsheet in BIG RED LETTERS for users who have to idea how to use the normal binocular feature, which is about 90% of the users.

## Matt Smith

(January 8, 2019 - 3:56 pm)You have soooooo many videos, it's awesome. But, that makes it hard to find the video that can answer my question. I have a column of email addresses (60,000 cells). I have another column of competitor domains (120 cells) i.e. @competitordomain.com. I want to pull all of the emails out of the column with 60,000 records that have a domain that matches one of the 120 records. Will this lookup do it or is there a better way?

## Bob Armstrong

(January 8, 2019 - 3:56 pm)Hello – in this example the cell F2 is the search cell, if I want to search for 2 variables i.e Mann and Jo' can this be done? Love your examples and very helpful!

## Wilcey Whatweekindo

(January 8, 2019 - 3:56 pm)Hello. I would like a simple formula that will count the number of cells that contain only a partial word in excel. For instance, I would like to count the number of times that I have bought from a particular store, but in the worksheet, the cells don't always contain the exact text. Sometimes I copy and paste from the banks website, other times, I manually have put in the text into the cells. But I need a formula that will count all the cells that have a partial text or variation of it. Countif don't do it. Or at least I can't seem to do it right. It wants to count only exact references. Can u help me with this?

## ExcelIsFun

(January 8, 2019 - 3:56 pm)That is a lot of records. It would be best to have a helper column and use SEARCH and what ever other criteria you want in AND, then do a straight INDEX and MATCH or VLOOKUP. . Do not use an array formula – becasue it will be too slow. But a million rows, no matter what you do that will be slow, if you can do it at all…

Maybe this video:

Excel Magic Trick 802: Helper Column To Do Partial Text Lookup Formula To Return Multiple Records

## Barry Van Wyk

(January 8, 2019 - 3:56 pm)Hey man… I have question? I like this formula but want to do it with more then 979757 + records.. Help? excel don't want to..

## ExcelIsFun

(January 8, 2019 - 3:56 pm)In essence you must use SMALL and IF construction to replace Excel 2010 function AGGREGATE.

See: Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records

## ExcelIsFun

(January 8, 2019 - 3:56 pm)Try this video:

Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records

## Sebastian Mattos

(January 8, 2019 - 3:56 pm)how would you accomplish the same in excel 2007

## ExcelIsFun

(January 8, 2019 - 3:56 pm)That is awesome! I love to see that because it means that you have used the videos to really learn the concepts so you can create your own formulas! Great creativity!

## Matt Paul

(January 8, 2019 - 3:56 pm)so i saw this vid a while back – pretty much when it came out however i didnt duplicate the solution – after learning more and more about array formulas from your vids (thanks), i was able to come up with my own solution – Funny thing is it came out a bit like yours! Thanks for these:

IFERROR(INDIRECT("A"&MATCH(SMALL(IFERROR(SEARCH($A$1,$A$3:$A$39)+ROW($A$3:$A$39)/1000,""),ROWS($B$3:B3)),IFERROR(SEARCH($A$1,$A$3:$A$39)+ROW($A$3:$A$39)/1000,""),0)+2),"")

## ExcelIsFun

(January 8, 2019 - 3:56 pm)@BarefootPaul1 , I am sorry, but I do not know.

## ExcelIsFun

(January 8, 2019 - 3:56 pm)@YourXLNerd , simple is good!

## Matt Paul

(January 8, 2019 - 3:56 pm)i think mine is simpler – but this is by far more elegant . Thumbs up!

## ExcelIsFun

(January 8, 2019 - 3:56 pm)@krn14242 , cool!!

## krn14242

(January 8, 2019 - 3:56 pm)wow, great fuzzy search.