How may I retrieve data from an Excel table based on a variable number of criteria?

Question

I have the following salary data for example:

Country  State     2012  2013 -> 2027
=======  =====     ====  ====
China    Other     1000  1100
China    Shanghai  1310  1400
China    Tianjin   1450  1500
India    Orissa    1500  1600

So now in another Excel sheet I would want an answer to one of the following questions:

1. What is the salary in Shanghai for 2013? (Answer would be 1400)
2. What is the salary in Hubei province for 2012? (Since it is not listed, use "Other" - 1000)
3. What is the average salary in China for 2013? (Answer would be 1333)
4. What is the highest salary in China for 2012? (Answer is Tianjin)

So as in the above order of priority, I would like those numbers in another Excel sheet using some form of query. I considered PivotTables but I was wondering if there is another much better more efficient way of doing this?

I imagine SQL is suited for this but I am not clued up on that. Some Excel functionality is much rather preferred. Also suggestions on an appropriate format of data for such queries would be appreciated.

Solution

Your use case is perfect for Excel's database functions. To use them, you'll first need to unpivot your data so that each row corresponds to one observation in your data. This means you will have 4 columns: Country, State, Year and Salary.

Then you can use DGET() to get a specific entry in the database and DAVERAGE() to get an average. The criteria for database functions need to be laid out in pairs of cells where the filtering field goes on top and the value to be filtered goes underneath it. Here's a working example:

The formula for cell I10 is the same as the one for I9, except it uses DAVERAGE instead.

• In I9 enter =IFERROR(DGET($B$4:$E$12,$E$4,$G$4:$I$5),"")
• In I10 enter =IFERROR(DAVERAGE($B$4:$E$12,$E$4,$G$4:$I$5),"")

The IFERROR() around it ensures that DGET doesn't show #NUM! when you haven't provided values in all three inputs in row 6. This is because it can't find a single value for the salary if, say, you're not telling it which year to filter on.

Comments

[Dave]

"I imagine SQL is suited for this but I am not clued up" - Get clued up on it, it would be a doddle :) If the cells are hard coded, could you not just reference them via a $Sheet$?

[Eshwar]

I would gladly learn SQL but I have to share this model with co-workers and yeah they're not very technically inclined or have that kind of time!

[airstrike]

I'd start by unpivoting the data so that you have 4 columns: Country, State, Year, Salary. Then make it into a pivot table and use GETPIVOTDATA() to pull the data you want.

[airstrike]

Better yet, use database functions!

[Michael]

I guess you meant MATCH and not MATH? and also I think it should be noted that the use of semicolon or comma depends on the locale - usually it's a comma.

[airstrike]

If you'd like to have a flexible number of parameters, you could potentially build the list of criteria for, say, DAVERAGE() by using a dynamic named range