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.


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

  • 2
    Thanks, but I think you missed the part where I said, "by using the column name ... and the row name". :) – user6757 May 12 '13 at 20:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy