IF? formula to change cell if it contains one of several options

[GaryCee]

Hello, thanks for any help you might be able to give me. My knowledge of formulas is limited so apologies if I'm not very clear.

Our supplier is split into 4 divisions, with each division having multiple short codes for each branch - see below for an example (there can be 30+ short codes for each division but I have just given a small example).

[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD]Pipe
[/TD]
[TD]Drain
[/TD]
[TD]Plumb
[/TD]
[TD]Climate
[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]QH[/TD]
[TD]BAT[/TD]
[TD]G52[/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]QDL[/TD]
[TD]BGB[/TD]
[TD]THM[/TD]
[/TR]
[TR]
[TD]BS[/TD]
[TD]P12[/TD]
[TD]BGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BY[/TD]
[TD]HGH[/TD]
[TD]BH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K13[/TD]
[TD]FL[/TD]
[TD]BRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K16[/TD]
[TD]FJ[/TD]
[TD]BXD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K66[/TD]
[TD][/TD]
[TD]CH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K68[/TD]
[TD][/TD]
[TD]CT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have the statement in an excel spreadsheet downloaded from the supplier website so I can keep a tally of all the invoices that come in. The statement only shows the Branch Short Codes (C) but I would like to create a formula so it also shows the Division in column F....

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Invoice
[/TD]
[TD]Date
[/TD]
[TD]Branch
[/TD]
[TD]Amount
[/TD]
[TD]Type
[/TD]
[TD]Division
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1[/TD]
[TD]18/03/2017[/TD]
[TD]BP[/TD]
[TD]£1.00[/TD]
[TD]Invoice[/TD]
[TD]Pipe[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2[/TD]
[TD]18/03/2017[/TD]
[TD]QH[/TD]
[TD]£1.00[/TD]
[TD]Invoice[/TD]
[TD]Drain[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3[/TD]
[TD]20/03/2017[/TD]
[TD]BXD[/TD]
[TD]£1.00[/TD]
[TD]Invoice[/TD]
[TD]Plumb[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]21/03/2017[/TD]
[TD]BY[/TD]
[TD]£1.00[/TD]
[TD]Invoice[/TD]
[TD]Pipe[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]21/03/2017[/TD]
[TD]CT[/TD]
[TD]£1.00[/TD]
[TD]Invoice[/TD]
[TD]Plumb[/TD]
[/TR]
</tbody>[/TABLE]

I have tried using an IF formula in Column F but have just tried to add another branch sort code to it, and it tells me the formula is now too long - this is a much shortened version of what I have been using =IF($C$2:$C$999="BS","Pipe",IF($C$2:$C$999="QH","Drain",IF($C$2:$C$999="BRT","Plumb")))

Any help is much appreciated.

Thanks

 

[ExcelSpeedster]

You can do this by creating a "Master" table of Divisions and Branches. Simply create 2 headings, Division and Branch, then add the names of the divisions under the Division heading, and the names of their respective branches under the Branch heading.

This is how you should store data in Excel so that it will allow you to lookup a Division if you have a Branch.

If you enter Branches in column A and Divisions in column B (in a sheet called Master), you can use the following formula in cell F2 (and onwards) to lookup the Division from a Branch:
=VLOOKUP(C2,Master!$A:$B,2,false)

See this page for more information about the VLOOKUP formula. https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

 

[GaryCee]

This is perfect. Thank you so much for your help!