Nov 17 2020 10:02 AM
IF Formulathis fails
=IF(ISNUMBER(SEARCH(grocery,A1)),B1,”0”)
Nov 17 2020 10:23 AM
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))
Nov 18 2020 06:41 PM
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
Nov 19 2020 12:23 AM
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.
Dec 28 2020 01:16 PM
Yes the space did get in the way, thanks. better now