1

I have a table that looks something like this in Google Sheets:

A B C
2 Others Maufacturing Manufacturing
3 Manufacturing MAnufacturing Manufacturing
4 Others Others Others
5 Others Others

I want to add a formula in column C that will return:

  • IF A2="Others" return the value in B2. Since B2="Manufacturing" hence C2="Manufacturing"
  • IF A3="Manufacturing" return the value in A3. Hence C3="Manufacturing"
  • If A4="Others" and B4="Others" return B4. Hence C4="Others"
  • If A5="Others" and B5=""returnA5. Hence C5="Others"`

There are 100s of unique values in column A.

I have tried the following formula:

=IFS(ISBLANK(A2), "",
     OR(A2="Manufacturing", A2="Manufacturer"),  "Manufacturing",
     OR(A2="Others", A2="Other"), "Others" )
1
  • is the value "Others" actually a placeholder for multiple different values in your table? e.g. "Others" = OR("Other01", "Other02", "Other03", "Other04", etc.) and why do you mention "Manufacturer" in your formula? Is that another placeholder for an array of possible values? PS: I added in the missing double quotes and closing bracket to your formula so that it doesn't error out.
    – Blindspots
    Apr 3, 2023 at 19:36

2 Answers 2

0

There are various errors in your formula, including value's names. Try this:

=IFS( 
ISBLANK(A2),"", 
or( and (A2="Others", B2="Manufacturing"),and (A2="Manufacturing", B2="Manufacturing")),"Manufacturing", 
or( and (A2="Others", B2="Others"),A2 ="Others"), "Others" 
)

If you were to use it with the arrayformula() function you will have to eliminate the "and" and "or" like this:

=arrayformula(
IFS( 
ISBLANK(A2:A),"", 
(A2:A="Others")*(B2:B="Manufacturing"),"Manufacturing",
(A2:A="Manufacturing")*(B2:B="Manufacturing"),"Manufacturing", 
(A2:A="Others")*(B2:B="Others"),"Others", 
A2:A ="Others","Others" 
)
)
2
  • I will try this and update here but the function is going to get too long!
    – Dhiraj D
    Mar 7, 2022 at 16:26
  • Explain better what column A contains and what you want to achieve
    – Daniele
    Mar 7, 2022 at 17:17
0

It seems like your goal is to return the first value <> "Others".

Try this in C2:

=ArrayFormula(if((A2:A="Others")*(B2:B<>""),B2:B,A2:A))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.