0

I have a table which looks something like this in Google Sheet:

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

I want to add a C column which will show the distribution: IF A2 = 'Others', look in B2 for the value. B2= Manufacturing, hence in C2 it is updated to "Manufacturing" IF A3 = 'Manufacturing', Update C column to Manufacturing If A4="Others", B4="Others", update C4 = "Others" If A5= "Others", B5="", update C5="Others

There are actually around 100s of unique values in A column in original sheet and I got successful in updating C column based on just column A. Most of the values in C column are updated to 'Others' and we need to know what is in Column B for the same. I have used the following formula: =IFS(ISBLANK(A2),"",OR(A2="Manufacturing",A2="Manufacturer),"Manufacturing" ,OR(A2="Others",A2="Other),"Others"

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 at 16:26
  • Explain better what column A contains and what you want to achieve
    – Daniele
    Mar 7 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, privacy policy and cookie policy

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