Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
"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$? – Dave Oct 15 '12 at 10:04
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! – Eshwar Oct 20 '12 at 7:24

I think one way is to name the column - For example, right click on the cell with the value 1400 (salary for Shanghai for 2013) and define name

Then, in another cell or just refer to that cell - the beauty of this is it doesn't matter if that cell is moved, it will remain with the content you want.

share|improve this answer

With some preparation, this can also be easily done in Excel too. (Although might be easier and more natural with SQL).

The preparation: use named ranges as @Adam suggested - country for the Country, state for the State, and I also named each of the year in this format year2012.

Then you can create your answers sheet

     A              B         C         D
1 salary in     Shanghai    2013    =IFERROR(INDEX(INDIRECT("year"&C1),MATCH(B1,state,0)),INDEX(INDIRECT("year"&C1),MATCH("Other",state,0)))
2 salary in     Hubei       2012    =IFERROR(INDEX(INDIRECT("year"&C2),MATCH(B2,state,0)),INDEX(INDIRECT("year"&C2),MATCH("Other",state,0)))
3 average       China       2013    =AVERAGEIF(country,B3,INDIRECT("year" & C3))
4 highest       China       2012    {=INDEX(state,MATCH(MAX(IF(country=B4,INDIRECT("year"&C4))),INDIRECT("year"&C4),0))}

Each of these lines are dynamic, so you can change your 'questions' as required. The important thing to notice is that the 4th (highest) formula is Array Formula which requires the use of CTRL+SHIFT+ENTER when entering it.

p.s. Your answer to the 3rd question is wrong, the average salary in China in 2013 should be 1333.

share|improve this answer
thanks :D fixed... now let me try the solutions! – Eshwar Oct 20 '12 at 7:28

I agree with Dave Rook, that it is the place where you should use SQL.

It is also possible to be done entirely with Excel formulas (what isn't?). This is how:

First of all you will need a way of addressing each column. You have two options for that.

  1. First, (like Dave Rook suggested) straightforward and more "beautiful", is to define named ranges. Just be sure, that each named range is big enough to contain all future records (or use some sort of dynamic named range). Unfortunately it require some clicking for each column, so it doesn't scale when you have 100 columns.

  2. The other option (which I prefer for heavy-duty computations) is to generate the column range addresses strings using e.g. =ADDRESS(ROW(C3);COLUMN(C3);4;;"sheet1") & ":" & ADDRESS(ROW(C3)-1+$A$2;COLUMN(C3);4) for accessing data on column C with number of records stored in cell A2. Then you address the range by use of INDIRECT() formula everywhere, when you would normally paste the named range from method 1.

Once all your columns (fields) are named ranges, you can perform all sort of lookup and reference tasks on them.

For instance: to identify the number of record which contains the word "Shanghai" you would use =MATCH("Shanghai";State;0), where State is the named range for the column "State".

Once you have the record number (let's say it is in cell X10, you can get information about Salary with =INDEX(Salary;X10).

All sorts of tasks are possible in Excel formulas; e.g. you can create a list of unique values (or values that match certain criteria), you can compute any form of sum/average. You can even sort them (yes!).

Please note, that in my locale I use semicolon ; as argument separator (not comma). English-speaking countries usually use ,, and in that case to use my formulas you would need to replace all ; -> ,.

share|improve this answer
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. – Michael Oct 16 '12 at 8:41
Of course, thank you. – Adam Ryczkowski Oct 16 '12 at 8:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .