MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional array - If a text string is met in one cell, display result in another cell?


Posted by Dan on January 22, 2001 11:52 AM

How do I create an array with a conditional statement which looks for a value (such as the character "*") conatined in one cell and then if it finds that *, then displays the result of a second cell in a third cell.

In other words, what I want is:

If an asterisk is found in A, then take the amount of B and display it in C.

If NO asterisk in A, then show " " (nothing) in C.


A B C
1 FOOD PRICE *PET FOOD?
2 Bananas * $1.00 =IF(A2="*",B2,"")
3 Toothpaste $5.00 =IF(A3="*",B3,"")
4 Grapes * $3.50 =IF(A4="*",B4,"")


For some reason the array I have written does not work - Is that because it is reading the asterisk as a wild card and looking for ANYTHING to show up in the A columns?

It does not seem to be picking up the * in the A columns to trigger column B to display the results ultimately in column C, where they can be added up.

I tried substituting another symbol (!) instead of the *, but it still doesn't work. Where is my flaw?

Or instead of IF(A2= ...), maybe it should somehow say,

IF (A2 CONTAINS "*", then display B2 in column C2, or if not, display a
blank"")

How do you say "Contains" a character such as *, rather than is = to?

Please reply back to both Halfdan@aol.com AND to dmeyer@ena.com


Posted by Mark W. on January 22, 2001 11:57 AM

=IF(ISNUMBER(FIND("*",A2)),B2,"")

Posted by Dave Hawley on January 22, 2001 10:11 PM


Hi Dan

You Cannot use the FIND function to find an asterisk, you have to use the SEARCH instead and precede it with a tilde (~) like:

=SEARCH("~*",A1)

OzGrid Business Applications

Posted by Mark W. on January 23, 2001 7:28 AM

Dan, you most certainly can use an asterisk with the
FIND() function. My formula, =IF(ISNUMBER(FIND("*",A2)),B2,""),
works just fine. Evidently, Dave is misreading the
help documentation for the FIND() function. While
it's true that you can't use an asterisk and expect
for it to behave as a wildcard there is no reason
not to use the FIND() function to search for an
asterisk. In fact, if Dave had tried my solution
he would have discovered that it works as advertised!

Posted by Dave Hawley on January 23, 2001 5:47 PM


Sorry Dan, I missunderstood your needs.

Mark, I was not saying your formula was not suited I simply misread the Q. We are all here to help after all. Chill out :O)

Dave

OzGrid Business Applications