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

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/as...260381033.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

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

Try...

D1, copied down:

=IF(ROWS(D$1:D1)<=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$1:D1))),"")

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

Hope this helps!

In article . com,
wrote:

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/as...260381033.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

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

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 wrote:
Try...

D1, copied down:

=IF(ROWS(D$1:D1)<=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$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article . com,
wrote:

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/as...260381033.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

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

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

=IF(ROWS(D$1:D1)<=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$1:D1))),"")

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

Hope this helps!

In article . com,
"bdh_google" wrote:

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

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

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

=IF(ROWS(D$1:D1)<=COUNTIF(A$1:A$7,"*"&$C$1&"*"),IN DEX(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$1:D1))),"")

C1 = sh

This would match strings like:

Fish
Sheep
Trisha

Biff

"Domenic" wrote in message
...
Assuming that C1 contains the search value of interest, such as 'sh',
try the following formula instead...

=IF(ROWS(D$1:D1)<=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$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article . com,
"bdh_google" wrote:

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