formulasgoogle sheets
I have a list of transactions in column A that I want to categorize.
I want to search A1 for, say, "Shoprite" and if found return "Groceries", otherwise search for "Shell" and return "Gas", otherwise search for "Netflix" and return "Entertainment"
I tried (among other things):
=IF(FIND("Shoprite",A1), "Groceries", IF(FIND("Shell",A1),"Gas", IF(FIND("Netflix", A1),"Entertainment")))
Somehow this seems appropriate.
=ARRAYFORMULA(
QUERY(
{
IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""),
B2:B
},
"SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"
)
)
copy / paste
=ARRAYFORMULA(QUERY({IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), B2:B}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"))
If you also make the argument a range, also the cells your describing in the question do not match the sample sheet you shared - based on the sheet and the cells it is referring to, this is the formula that works:
=IFERROR(ARRAYFORMULA(FIND(B12:B18, B3:B9)))
IFERROR basically avoids showing #N/A when the value is not yet found, and since you want to apply the find function to the list of names you have written you need to pass it a range of names to look up, completed by wrapping it with ARRAYFORMULA .
If you share a more complete sheet that is closer to the actual results your trying to get it would be easier to come up with a more dynamic answer, likely a simpler way to do it also .
To match in any order you should move your search list to another column or sheet , pretend you move your list of names to start in cell I2 you would update your formula and enter it in H2:
=IFERROR(ARRAYFORMULA(FIND(H2:H, B2:B)))
Best Answer
try like this:
if A1 doesn't say exactly
Shoprite, but ratherShoprite on 7th, then use this: