 # formula fails

IF Formulathis fails

=IF(ISNUMBER(SEARCH(grocery,A1)),B1,”0”)

5 Replies

# Re: formula fails

In formula around o you use curve apostrophes instead straight ones, thus formula returns NAME error.

Even if correct it's not clear what you'd like too find, formula searches array of texts within cell value (A1) and returns array with results. on pre-DA Excel it returns first element of such array, with dynamical arrays entire array and on next cell you'll have SPILL error.

Perhaps you mean something like

``=FILTER(B1:B6,COUNTIF(grocery,A1:A6))``

# Re: formula fails

Thons for the Quotes help, I had not known the difference. the formula is supposed to find matching term in an array and return prices in adjacent column

# Re: formula fails

=Filter did not seem to function

# Re: formula fails

Sorry, but still not clear.

@Mike Burke wrote:

Thons for the Quotes help, I had not known the difference. the formula is supposed to find matching term in an array and return prices in adjacent column

There are prices here, in column B? Under array you mean range F1:F11, correct? Or array is in column A? If take concrete value, e.g. Union, shall we consider "   Union" and "Union" as the same value (i.e. trim only spaces) or "Union One" also is considered as the same.

If value of A4 is found in F1:F11 we return 291 in D4 or it works in opposite way.

Perhaps you may manually add desired result, that will be much more clear.

# Re: formula fails

Yes the space did get in the way, thanks.  better now