Google Spreadsheets Index-Match

1
votes

I'm having difficulty writing the correct formula to find the value of a cell at the intersection of dynamically chose row and column. The choices for row and column are populated by data validation.

A very simple version using a colour mixing example can be found here: https://docs.google.com/spreadsheet/ccc?key=0AsOxG6LF9b4XdEliSlMzQl94UnJWOF9IZVducHRNS1E&usp=sharing

Which uses the following formula: 

=INDEX(A1:D4; MATCH(B9,A2:A4,0); MATCH(C9,B1:D1,0))

What am I doing wrong here? Is this the best method for finding data in a simple table using multiple criteria?

1
indexinggoogle-sheetsmatch

1 Answers

0
votes

The first argument just needs to be the array you are indexing, and therefore shouldn't include the column and row headers:

=INDEX(B2:D4;MATCH(B9;A2:A4;0);MATCH(C9;B1:D1;0))