Return single value based on several drop down lists

[sirbudan82]

I'm looking for a formula that will provide one single value based on several drop down lists selection.
For example :
Department (drop down list) : Medical | Marketing | Customer Service
Country ( drop down list) : Japan | US | Brazil
Expenses (drop down list) : Consulting Fees | Adboard | Digital Expenses
Cost Centers (not drop down list) : 1234 US Medical Expenses 1235 Japan Marketing Expenses 1236 Brazil Digital Expenses

I would select "Medical" then "US" and "Consulting Fees" and the formula would provide only one value which would be cost center : 1234.

I managed to use multiple IFs and to make it work, the problem is that the lists are getting bigger and bigger and the formula is huge, I was wondering if there is another way.

Thank you

 

[Snakehips]

Assuming that you will have a table defining the Cost Centers then, maybe like.....

Excel 2010
	A	B	C	D	E	F	G
1		Picks				List Picks	Cost Center
2	Department	Customer Service				USMedicalConsulting Fees	1234
3	Country	Brazil				JapanMarketingExpenses	1235
4	Expenses	Adboard				BrazilCustomer ServiceAdboard	1236
5	Cost Center	1236				USMarketingConsulting Fees	1237
6
Sheet1

Cell Formulas
Range	Formula
B5	=INDEX(G1:G21,MATCH(B3&B2&B4,F1:F21,0))

Hope that helps.

 

[sirbudan82]

Many thanks, it worked.