Index Match Strategy with Multiple Criteria

[mm3904]

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?

 

[mm3904]

Is it possible upload screen shots or sample Excel sheets?

 

[mm3904]

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.

 

[sailepaty]

Try this


=LOOKUP(2,1/(Dates Range=vDate)*(Class Range=vClass)*(FX Range=vFX),Return Range)


Regards

 

[Debraj]

Hi mm3904!


Try Below..

=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

 

[mm3904]

Great, thanks for the replies!