# Questions on how to write formulas using Arrayformula, IFS, and FIND functions in Google spreadsheets

Asked 3 months ago, Updated 3 months ago, 11 views

in the spreadsheet "If there is a letter ""Apple"" in the cell of A, I would like to display 1 in the cell of B, 2 in the cell of B, and 3 in the cell of mandarin orange, and apply it to all the cells below."

I'm trying to make that formula using Arrayformula, IFS, and FIND functions, but it's not working.

I think the way the formula is written is wrong, but if you don't mind, could you tell me the correct way to write it?Or, if there is another way, please let me know.

2022-09-30 11:46

`IFS` is not used, but it can be achieved using the following formula:

``````=ARRAYFORMULA(IF(ISERR(FIND("Apple", A1:A))), IF(ISERR(FIND("Banana", A1:A))), IF(ISERR(FIND("Mikan", A1:A))), ", 3), 2))
``````

`FIND` returns `#VALUE!` when no string is found, so we used `ISERR` for error determination.

I have never used `IFS`, so I used `IF`.

The finished ceremony is long and difficult to correct.
If you want to add or change features, it may be easier to recreate them, so I'll show you how to create them.

An expression that returns a given value if the string is found, or 999 if it is not found.

``````=IF(ISERR(FIND("Apple", A1)), 999, 1)
= IF(ISERR(FIND("Banana", A1)), 999, 2)
= IF(ISERR(FIND("Tangerine", A1)),999,3)
``````

Replace the first expression 999 with the following expression:

``````=IF(ISERR(FIND("Apple", A1)), IF(ISERR(FIND("Banana", A1)), 999, 2), 1)
``````

Returns a given value if the desired string is found, or returns the result of the built-in expression.

In the same way, incorporate the following formula:

``````=IF(ISERR(FIND("Apple", A1)), IF(ISERR(FIND("Banana", A1)), IF(ISERR(FIND("Mikan", A1)), 999, 3), 2)
``````

Apply ARRAYFORMULA().

``````=ARRAYFORMULA(IF(ISERR(FIND("Apple", A1)), IF(ISERR(FIND("Banana", A1)), IF(ISERR(FIND("Mikan", A1)), 999, 3), 2))
``````

Set the coverage to all cells in column A.< Replace `A1` with `A1:A`.

``````=ARRAYFORMULA(IF(ISERR(FIND("Apple", A1:A))), IF(ISERR(FIND("Banana", A1:A))), IF(ISERR(FIND("Mikan", A1:A))), 999, 3), 2))
``````

Replace 999 with an empty string.That's it.

``````=ARRAYFORMULA(IF(ISERR(FIND("Apple", A1:A))), IF(ISERR(FIND("Banana", A1:A))), IF(ISERR(FIND("Mikan", A1:A))), ", 3), 2))
``````

2022-09-30 11:46

## If you have any answers or tips

Popular Tags
python x 4566
android x 1593
java x 1484
javascript x 1415
c x 916
c++ x 866
ruby-on-rails x 694
php x 688
python3 x 675
html x 650