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.

Enter a description of the image here

google-spreadsheet

2022-09-30 11:46

1 Answers

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


© 2023 OneMinuteCode. All rights reserved.