use wildcard to return multiple items from one lookup value

[dkmanley]

I have a large dataset, rows and columns. Column 1 (name), column 2 (another name), column 3 (email). I have a single lookup that spans the 2 "name" columns, and returns the 3 matching columns. Currently it works but you have to plug in the exact name to get a return. Here is the formula: =IFERROR(INDEX(N$2:N$65000,AGGREGATE(15,6,(ROW($N$2:$N$65000)-ROW($N$1))/($N$2:$N$65000=$B$1),ROWS(D$2:D2))),"") ... $B$1 is the lookup and I've tried "*"&$B$1&"*" which works with the count formula ... =COUNTIFS($N$2:$O$65000, "*"&$B$1&"*") but not the Index ... formula. Image attached as example. If I type "20TH CENTURY FOX" in B1, it pulls 5 rows of unique data. If I try the wildcard function in the "Index" formula, and type "fox" , no data pulled but the Countifs function finds 26 instances.

[TABLE="width: 888"]
<colgroup><col width="270" style="width: 203pt; mso-width-source: userset; mso-width-alt: 9874;"> <col width="267" style="width: 200pt; mso-width-source: userset; mso-width-alt: 9764;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5412;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <tbody>[TR]
[TD="class: xl68, width: 270, bgcolor: #5F7A76"]Type partial name for lookup[/TD]
[TD="class: xl66, width: 267, bgcolor: transparent"]fox[/TD]
[TD="width: 22, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 148"]Ship To Name[/TD]
[TD="class: xl69, width: 194"]AMID L2 Name[/TD]
[TD="class: xl69, width: 281"]Email Address[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #5F7A76"]Recurences in list[/TD]
[TD="class: xl67, bgcolor: transparent"]26[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 888"]
<colgroup><col width="270" style="width: 203pt; mso-width-source: userset; mso-width-alt: 9874;"> <col width="267" style="width: 200pt; mso-width-source: userset; mso-width-alt: 9764;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5412;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <tbody>[TR]
[TD="class: xl68, width: 270, bgcolor: #5F7A76"]Type partial name for lookup[/TD]
[TD="class: xl66, width: 267, bgcolor: transparent"]20TH CENTURY FOX[/TD]
[TD="width: 22, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 148"]Ship To Name[/TD]
[TD="class: xl69, width: 194"]AMID L2 Name[/TD]
[TD="class: xl69, width: 281"]Email Address[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #5F7A76"]Recurences in list[/TD]
[TD="class: xl67, bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]20TH CENTURY FOX[/TD]
[TD="class: xl69, bgcolor: transparent"]TWENTY-FIRST CENTURY[/TD]
[TD="class: xl69, bgcolor: transparent"]aaron.li@[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]20Th Century Fox[/TD]
[TD="class: xl69, bgcolor: transparent"]TWENTY-FIRST CENTURY[/TD]
[TD="class: xl69, bgcolor: transparent"]Jeff.Dow@[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]20TH CENTURY FOX[/TD]
[TD="class: xl69, bgcolor: transparent"]AVNET INC[/TD]
[TD="class: xl69, bgcolor: transparent"]jennifer.mosley@[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]20TH CENTURY FOX[/TD]
[TD="class: xl69, bgcolor: transparent"]AVNET INC[/TD]
[TD="class: xl69, bgcolor: transparent"]kyle.peterson@[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]20TH CENTURY FOX[/TD]
[TD="class: xl69, bgcolor: transparent"]AVNET INC[/TD]
[TD="class: xl69, bgcolor: transparent"]pamela.spores@[/TD]
[/TR]
</tbody>[/TABLE]

 

[dkmanley]

Still having no luck

 

[63falcondude]

To use wildcards in a non-COUNTIF or SUMIF formula, try using COUNTIF($N$2:$N$65000,"*"&$B$1&"*") or ISNUMBER(SEARCH($B$1,$N$2:$N$65000) within your formula.

 

[dkmanley]

The second option worked great. I tried to get the COUNTIF to work with no luck. Plugged in ISNUMBER ... and it worked great. Thanks.

=IFERROR(INDEX(H$2:H$41018,AGGREGATE(15,6,(ROW($H$2:$H$41018)-ROW($H$1))/ISNUMBER(SEARCH("*"&$B$1&"*",$H$2:$H$41018)),ROWS(D$2:D2))),"")

Thanks. dkmanley