Monday, 1 June 2015

How to lookup a value based on the Row and Column Match in Excel

Look up Intersecting Value by Row and Column Criteria
Suppose we have a  Product Sales Data Table as follows :

Prod_ID/Sales_Period
Q1-2014
Q2-2014
Q3-2014
Q4-2014
ABC_1234
157872
223764
163852
315189
BCD_2345
207254
215188
225615
281984
CDE_3456
222382
140506
156293
239015
DEF_4567
224419
254021
295131
292883
EFG_5678
315015
222710
310269
272110
FGH_6789
207663
200940
193089
311924
GHI_7890
221794
161097
185287
233228

From the above Data if you want to Lookup the Sales of the Prod_ID="DEF_4567" for the Sales_Period="Q3-2014" , we can use the below Lookup methods :

Index_Match Lookup Method
Offset_Match Lookup Method

Column Criteria :
Q3-2014
Row Criteria :
DEF_4567
Lookup : Index_Match Method
 =INDEX($A$1:$E$8,MATCH("DEF_4567",$A$1:$A$8,0) MATCH("Q3-2014",$A$1:$E$1,0))
Lookup : Offset_Match Method
 =OFFSET($A$1,MATCH("DEF_4567",$A$1:$A$8,0)-1, MATCH("Q3-2014",$A$1:$E$1,0)-1)
Result :
295131

Note :
For other Lookup methods, please go through the below links of this Blog :


Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts