• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Strategy with Multiple Criteria

mm3904

New Member
Hello - I am trying to use index match to locate values based on two criteria. The column I want to return is return. I need to match this to a specific date, a fund name and a currency. Thus, there are three different criteria that needs to be met in order to match the right data. Here is the reference data:


Date Class FX Return

3-Jan-12 AUC USD 1

3-Jan-12 BUC USD 11

3-Jan-12 CGE USD 12

3-Jan-12 DSE USD 13

3-Jan-12 DSE SGD 16

3-Jan-12 ESC SGD 39

3-Jan-12 ESC USD 37

4-Jan-12 AUC USD 11

4-Jan-12 BUC USD 15

4-Jan-12 CGE USD 65

4-Jan-12 CGE GBP 62

4-Jan-12 DSE SGD 16

4-Jan-12 DSE USD 12

4-Jan-12 ESC SGD 94

4-Jan-12 ESC USD 72

5-Jan-12 AUC USD 11


Here is the output criteria:

CGE CGE BUC

USD GBP USD

1/3/2012 12 62 11

1/4/2012 65 15


Essentially, I need to index return and match the values based on the date, class and FX. Any thoughts?
 
The reference data has four columns: date, class (three letters), FX(three letters) and return.


The output is based on three columns: date, class and FX.
 
Hi mm3904!


Try Below..


Code:
=SUMPRODUCT((Dates_Range=vDate)*(Class_Range=vClass)*(FX_Range=vFX),Return_Range)


Its more flexible than.. INDEX & MATCH.. as below..


{=INDEX(Return_Range,MATCH(1,(Dates_Range=vDate)*(Class_Range=vClass)*(FX_Range=vFX),0))}


confirm be CTRL + SHIFT + ENTER.. not just Enter..


https://dl.dropbox.com/u/78831150/Excel/index%20Match%20Strategy%20with%20Multiple%20CriteriA%28mm3904%29.xls


Regards,

Deb
 
Back
Top