Solved: Excel - how to use wildcards with array with IF statement

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

holly00

Thread Starter
Joined
Jul 27, 2007
Messages
26
Hi all,

I have the following array formula:

=INDEX($B$1:$C$252,SMALL(IF($C$1:$C$252="A",ROW($B$1:$B$252)),ROW(1:1)),1)

where I would like to use a wilcard with the A but this doesn't work:
=INDEX($B$1:$C$252,SMALL(IF($C$1:$C$252="*A*",ROW($B$1:$B$252)),ROW(1:1)),1)


Does any one know how to convert this formula to use a wildcard???

Thanks in advance!
 
Joined
Sep 24, 2007
Messages
869
HI!
Could you please explain what should do you function!
And what happens when there's no "A" in the searched cell (if statement is false)?

It would be better if you could post a sample file so we could see how it works!
 

holly00

Thread Starter
Joined
Jul 27, 2007
Messages
26
Hi AJ,
Thanks for your reply. I have attached a sample file of how I would like to use the formula.

Basically at the moment the function in cell F3 selects only the tasks from column A where the "Who" (column B) ="Anne".

What I am trying to do is select all the tasks which contain "Anne" as a "Who", but unfortunately "*Anne*" does not work in this function.

Any ideas how to modify it?
Thanks again.
 

Attachments

Joined
Sep 24, 2007
Messages
869
Did you tried Autofilter?
It will do the work you wanna, without any formulas!
 

holly00

Thread Starter
Joined
Jul 27, 2007
Messages
26
I need to put the results in another spreadsheet so using CONTAIN in the autofilter will not help...
Any other ideas?
Thanks again.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top