Excel 2010 Index Match using multiple criteria

Question

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?

excel-easy.com/examples/two-way-lookup.html

Tim Williams
– Tim Williams

2015-04-30 20:59:31 +00:00
Commented Apr 30, 2015 at 20:59 — Tim Williams
– Tim Williams, Commented Apr 30, 2015 at 20:59

nbayly · Accepted Answer · 2015-04-30 22:11:01Z

2

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,

answered Apr 30, 2015 at 22:11

nbayly

2,1672 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel 2010 Index Match using multiple criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related