Retrieve a cell having a column and a row header in Google Spreadsheets

Question

I have a spreadsheet in Google Docs like this:

           Johan    Mark    Gil    Pablo

Course 1      45      50     80       50
Course 2     250     300     50      270
Course 3      19      50     29       15
Course 4     270     250    250      250
Course 5       5       5     11       50
Course 6      45     250    250       17

I would like to retrieve the content of a cell given a column and a row headers.

The example here shown: the column header is "Pablo" and the Row header is "Course 3".

What's the function that retrieves the content of the cell in the matrix? (Resulting value should be "15".)

Answer 1

This will work, based on a table with upper-left corner in A1, modify to suit actual:

=INDEX(A1:E7, MATCH("Course 3", A:A, 0), MATCH("Pablo", 1:1, 0))

Answer 2

This is driving me crazy!

I have the same problem. If I insert the function on the same sheet it works perfectly.

If I insert the function on another sheet and of course point to the same cells using the sheet function, if doesn't work and gives me another value!

This is the version that doesn't work. Its on another sheet but the value returned is from the correct row but the wrong column. =INDEX(Worksheet!A1:BU100, MATCH("aapl", Worksheet!A:A, 0), MATCH("Annual Dividend", 1:1, 0))

This version I tested on the same sheet as the table and works perfectly. =INDEX(A1:BU100, MATCH("aapl", A:A, 0), MATCH("Annual Dividend", 1:1, 0))

Make things stranger? If I use the top version with the worksheet reference on the sheet with the table it also works.

Is this a bug? Or am I doing something wrong?