formula fails

%3CLINGO-SUB%20id%3D%22lingo-sub-1897273%22%20slang%3D%22en-US%22%3Eformula%20fails%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1897273%22%20slang%3D%22en-US%22%3E%3CP%3EIF%20Formulathis%20fails%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(SEARCH(grocery%2CA1))%2CB1%2C%E2%80%9D0%E2%80%9D)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1897273%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1897358%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20fails%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1897358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144141%22%20target%3D%22_blank%22%3E%40Mike%20Burke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20formula%20around%20o%20you%20use%20curve%20apostrophes%20instead%20straight%20ones%2C%20thus%20formula%20returns%20NAME%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEven%20if%20correct%20it's%20not%20clear%20what%20you'd%20like%20too%20find%2C%20formula%20searches%20array%20of%20texts%20within%20cell%20value%20(A1)%20and%20returns%20array%20with%20results.%20on%20pre-DA%20Excel%20it%20returns%20first%20element%20of%20such%20array%2C%20with%20dynamical%20arrays%20entire%20array%20and%20on%20next%20cell%20you'll%20have%20SPILL%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(B1%3AB6%2CCOUNTIF(grocery%2CA1%3AA6))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1904961%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20fails%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1904961%22%20slang%3D%22en-US%22%3E%3CP%3E%3DFilter%20did%20not%20seem%20to%20function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

IF Formulathis fails

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

4 Replies
Highlighted

@Mike Burke 

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))
Highlighted

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

Highlighted

=Filter did not seem to function

 

Highlighted

@Mike Burke 

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?

image.png

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.