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