Friday, 12 September 2014

How to Lookup or Find Last Matching Value in a List or Group of Values in Excel

Index-Match Function to Lookup or Find  Last Matching Item in a List or Group of Items in Excel.
Suppose we have a as Shown below , Prod_Id,Sales_Region,Sales_Period and Net_Sales.

From the Following data if you want to Find/Lookup the last Matching Product ID for the Period "Q1-2014", we can find using the below  "Index-Mactch-Countif " Combo formula.

=INDEX($A$1:$A$113,MATCH($E$2,$C$1:$C$113,0)+COUNTIF($C$1:$C$113,$E$2)-1)



=INDEX($A$1:$A$113,MATCH($E$2,$C$1:$C$113,0)+COUNTIF($C$1:$C$113,$E$2)-1)

In the above data , the first matching "Prod_Id" for the period "Q1-2014" is "ABC_1234" and the Last Matching value is "GHI_7890".

Note : 
If you want to get the desired last matching value , first you should sort the Lookup value Column.


Please provide your valuable comments or ask the queries that you have , I will be happy to help.

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts