Forum Discussion

SelenaJane73's avatar
SelenaJane73
Copper Contributor
Dec 20, 2023

Excel IF and OR

Hello I am trying to insert a formula:

 

I need text entered into a cell if cell A2 is administration then add Peter, or if cell A2 is Education then add John etc etc.  I basically want to add the correct Manager to the cell according to the team the staff member falls under.  

 

Admin = Peter

Education = John

Medical = Mary

Surgical = Harry

 

I hope this makes sense.

I have worked out how to enter 1 choice   =IF(H2="admin","Peter") , I am just unsure how to add more options using OR

 

Thanking you in advance

excel

4 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 20, 2023

    SelenaJane73 Try nested IF() formula-

    =IF(H2="Admin","Peter",IF(H2="Education","John",IF(H2="Medical","Mary",IF(H2="Surgical","Harry","Something Else"))))

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 20, 2023

    SelenaJane73 Although nested IF functions or IFS will work, you may want to avoid hard-coding all departments and manager names into the formula. Consider using a lookup table and use XLOOKUP or VLOOKUP (to name a few) to dynamically match the Department with the Manager as demonstrated in the attached file.

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Dec 20, 2023

    Hello SelenaJane73 

    If you're using a new version of Excel (2016 and above), IFS function is all you need.

    =IFS(A2="Admin", "Peter", A2="Education", "John", A2="Medical", "Mary", A2="Surgical", "Harry")


    Or if you're using an older version:

    =IF(A2="Admin", "Peter", IF(A2="Education", "John", IF(A2="Medical", "Mary", IF(A2="Surgical", "Harry", "No Match"))))

     

Resources