INDEX and 2 MATCHES (of which 1 variable)

[p.strijbosch]

Hello,

I'm stucked with the following: I try to retrieve a result (column
C)via a combination of a INDEX and two MATCH functions, the first MATCH
needs to be matched exactly but the second MATCH has to be defined as
the closest match.

Sample:
A B C
1 January 2005 Low Forecast PE <V,Lcl> 5
2 January 2005 Small Cap <S,lcl> 6
3 January 2005 High EBIT/EV <V,Lcl> 7
4 February 2005 Low Forecast PE <V,Lcl> 5
5 February 2005 Small Cap <S,lcl> 6
6 February 2005 High EBIT/EV <V,Lcl> 7

As the values in column B differ, it is desirable to look at the
desciption without the brackets (i.e. Low Forecast).

Which formula gives me the result in column C when machting the
following:
1. January 2005
2. Low Forecast <V>

For the sake of completeness I would like to underline that the fixed
description is i.e. Low Forcast <V> but that the desciption in the
above summary one time is <V> and the other time <V,Lcl>.

Hope that someone can tell me which formula to use.

Thanx in advance

Rgds,
Robert

 

[Domenic]

Try the following formulas that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(C1:C6,MATCH(1,(A1:A6="January 2005"+0)*(ISNUMBER(SEARCH("Low
Forecast",B1:B6)))*(ISNUMBER(SEARCH("V",B1:B6))),0))

OR

=INDEX(C1:C6,MATCH(1,(A1:A6=E1)*(ISNUMBER(SEARCH(F1,B1:B6)))*(ISNUMBER(SE
ARCH(G1,B1:B6))),0))

....where E1 contains your first criterion, such as January 2005, F1
contains your second criterion, such as Low Forecast, and G1 contains
your third criterion, such as V.

Hope this helps!