Web Applications Stack Exchange is a question and answer site for power users of web applications. 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

Suppose I have a workbook with two worksheets, data and view. data is structured in "database" style, i.e. with a single header row and unique keys in the first column, e.g.:

Country   GDP  Landlocked
Austria   418   1
France   2770   0
Spain    1480   0

In the view sheet, I wish to look up the data in data by using the column name (i.e. the value of the relevant cell in the header row in data) and the row name (i.e. the value of the relevant cell in the unique key column in data). Here is one way to do this, if I wanted to retrieve, e.g. the GDP value for France:


However, this is rather verbose and requires two functions. Are there any more concise ways to achieve the goal?

P.S. If you're wondering why I specified the first range above as data!$A$1:$YY, please see this question.

share|improve this question

If you did know the exact column of GDP then Jacob's function would be simpler.

If you do not know the exact column of GDP or row of France, then your function actually works quite well. You could also use Index(Match()) like this:


You can also name each column in 'Data'->'Named and Protected Ranges'. IE. the Name "GDP" would reference "data!B2:B". This would allow you to do the following:


enter image description here

This is very useful when using drop down menus or data validation. You can use the Indirect() function to automatically populate a drop down with choices based off a user input or other drop down menu.

share|improve this answer

If you're looking for just the GDP of France then this will do:

share|improve this answer
Thanks, but I think you missed the part where I said, "by using the column name ... and the row name". :) – sampablokuper May 12 '13 at 20:48

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.