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).

Pipe	Drain	Plumb	Climate
BP	QH	BAT	G52
BM	QDL	BGB	THM
BS	P12	BGE
BY	HGH	BH
K13	FL	BRT
K16	FJ	BXD
K66		CH
K68		CT
K83

<tbody>
</tbody>

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....

	A	B	C	D	E	F
1	Invoice	Date	Branch	Amount	Type	Division
2	1	18/03/2017	BP	£1.00	Invoice	Pipe
3	2	18/03/2017	QH	£1.00	Invoice	Drain
4	3	20/03/2017	BXD	£1.00	Invoice	Plumb
5	4	21/03/2017	BY	£1.00	Invoice	Pipe
6	5	21/03/2017	CT	£1.00	Invoice	Plumb

<tbody>
</tbody>

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!