excelgoogle-sheetsexcel-formulanested-if

IF Formula to auto populate cost center name basis the cost center number in excel

So I have a bunch of cost centers in column A. Basis the cost center number, I want the column B to be filled with cost center name automatically in excel. For example if the cost center number = 101 Or 105 Or 108 then the name in the Column B should be auto filled as "operations". if the cost center number is = 102, or 106 or 107, then the cost center name should be "marketing" and so on..

I tried writing below formula to achieve at least one condition but it is not working.

=IF(OR($A$2=101,$A$2=105,$A$2=108,"Operations",""))

Please help with an IF statement that combines And and OR conditions to fill the names basis the numbers in column A

| Cost Center Number | Cost Center Name | | ___________________| _________________| | 101

Solution

  • You misplace the true and false result into OR, simply move them out like so and it will work.

    =IF(OR($A$2=101,$A$2=105,$A$2=108),"Operations","")

    If you want to meet other criteria, you can add simply use IFS and OR.

    =IFS(OR(A2=101,A2=105,A2=108), "Operation", OR(A2=102,A2=106,A2=107), "Marketing")

    But in this case, your formular will become longer with more criteria. Hence, I would suggest using VLOOKUP. You need to create a lookup table in the sheet that represent the key and value:

    +---+----------+
|key|value     |
+---+----------+
|101|Operations|
|102|Marketing |
... and so on

    then use this formula to lookup the value

    =VLOOKUP(A2,'lookup table'!A:B,2)

    here is an example: https://docs.google.com/spreadsheets/d/1GDqV_UZGmtIT01bAJgP4PQgPzvU41QbxyLyDO2YhW3U/edit