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.

google-spreadsheet

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

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

Popular Questions

372 To find Python openpyxl value coordinates

354 Understanding How to Configure Google API Key

363 Logging Out with the Application Load Balancer and Authentication Using Cognito

364 winget install-e --id Microsoft.WindowsSDK fails.

360 I would like to know if I can retrieve data using pandas grouping.

© 2023 OneMinuteCode. All rights reserved.