12

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".)

2 Answers 2

13

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))
2
  • It works very well ! Many Thanks! :-)
    – user12248
    Jul 18, 2011 at 10:13
  • 2
    @pnuts, I agree, hardcoding isn't the best way. Jun 18, 2014 at 2:03
-1

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?

4
  • Solved it! Stupid type. Forgot to add sheet ref on the last range 1:1 -> worksheet!1:1
    – Jeff
    Apr 2, 2023 at 18:58
  • FYI, in the future, if you have a question, post a new question. A new question or comment posted as an answer is always deleted, as this one will be inevitably. Glad it worked out, but your experience can help someone else but only if it isn't deleted...
    – Blindspots
    Apr 3, 2023 at 19:05
  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
    – Blindspots
    Apr 3, 2023 at 19:05
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Apr 5, 2023 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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