Excel 2010 Index Match using multiple criteria

0
votes

I have a following reference table that I am using to extract the values

Table 1:

Name   Jan  Feb  Mar  Apr  Total 
John   0.1  0.2  0.3  0.4  1 
Peter  0.2  0.4  0.6  0.3  1.5
Suman  0.1  0.3  0.2  0.5  1.1

into this table-

Table 2:


Name   Month   Value 
John   Feb    
Peter  Apr 
Suman  Jan

I need to match the text in "Name" column of Table 2 with the text in "Name" column in Table 1..and match the text in "Month" column of Table 2 with the Column Name itself in Table 1 to return the value for that particular column name

For example:

Match value "John" in Table 2 with value "John" in Table 1 Match value "Feb" in Table 2 with Column Name "Feb" in Table 2 to return the value 0.2 from Table 1

Can this be done using Index-Match using multiple criteria?

1
excelexcel-formulaexcel-2010
excel-easy.com/examples/two-way-lookup.html - Tim Williams

1 Answers

2
votes

Assuming Table 1 starts in A1 and Table 2 starts in J1. In Column L ("Value" column) starting in cell L2 place the formula:

=INDEX($A$1:$F$4,MATCH($J2,$A$1:$A$4,0),MATCH($K2,$A$1:$F$1,0))

And copy down to all relevant rows.

The first match selects the row for the index by matching the name and the second match selects the column by matching the month. Hope this helps. Cheers,