Pulling multiple values from a list based on a wildcard search value?

[bdh_email]

I need some help w/ some excel coding. After reading the article "How
to look up a value in a list and return multiple corresponding values"
by Ashish Mathur
(http://office.microsoft.com/en-gb/assistance/HA012260381033.aspx) I
would like to use this code but modify it so my "search" value can
include wildcards...

So in reference to the example at the Microsoft website (above),
instead of searching for "Ashish", I would like to be able to search
for "????sh" and return the multiple values for both "Ashish" and
"Rajesh"

Unfortunately I can't get this working and was wondering if anyone
could lend a hand.

Thanks in advance!
-bdh

 

[Domenic]

Try...

D1, copied down:

=IF(ROWS(D$11)<=COUNTIF(A$1:A$7,"*sh"),INDEX(B$1:B$7,SMALL(IF(RIGHT(A$1
:A$7,2)="sh",ROW(B$1:B$7)-ROW(B$1)+1),ROWS(D$11))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[bdh_google]

I guess I was hoping for the search value to be more flexible, in that
it may be referenced from its own cell instead of within the actual
formula. That way if I were to search for other
options/characters/etc., I would not have to change the formula code
and would only have to change the single cell.

Thoughts?
-bdh

 

[Domenic]

Assuming that C1 contains the search value of interest, such as 'sh',
try the following formula instead...

=IF(ROWS(D$11)<=COUNTIF(A$1:A$7,"*"&$C$1),INDEX(B$1:B$7,SMALL(IF(RIGHT(
A$1:A$7,LEN($C$1))=$C$1,ROW(B$1:B$7)-ROW(B$1)+1),ROWS(D$11))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Biff]

To add even more flexibilty and be able to find the criteria *anywhere
within* the string:

=IF(ROWS(D$11)<=COUNTIF(A$1:A$7,"*"&$C$1&"*"),INDEX(B$1:B$7,SMALL(IF(ISNUMBER(SEARCH($C$1,A$1:A$7)),ROW(B$1:B$7)-ROW(B$1)+1),ROWS(D$11))),"")

C1 = sh

This would match strings like:

Fish
Sheep
Trisha

Biff