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

1 comment:

1. Excellernt site you have got here.. It's difficult to find good quaality writing lke yours nowadays.

I really appreciate people like you! Takee care!!

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.