R
Robert
Hello,
In a specific sheet I have two ranges of data, overweighted styles and
underweighted styles. From these ranges I would like to retrieve a
result that matches with a date as well as a description of a
particularly style. But as the styles sometimes switch between the two
ranges I have to extend the formula with an IF function, but I have no
clue how to combine this function with a MATCH/INDEX function.
To illustrate my problem please find below a sample:
A B C D E F
1 Date Overweighted Styles Nr Date Underweighted Styles Nr
2 March Low Forecast PE 23.0 March Low Dividend Cover -18.1
3 March High EBIT/EV 17.8 March Low Asset Backing 16.3
4 March Composite Value 16.9 March Composite Growth -15.8
5 March High Dividend Cover 13.7 March Low EBIT/EV -13.8
6 March High Asset Backing 8.0 March High Forecast PE -13.7
7 March High Debt to EV 7.3 March HM Term Price Mom -8.6
8 March Mid Cap 5.3 March High ROIC -7.5
9 March Low ROIC 2.8 March Large Cap -7.2
10 March LMT Price Momentum 2.7 March Earnings Downgrades -7.2
11 March High Dividend Yield 1.7 March Low Debt to EV -5.6
12 June Low Forecast PE 17.9 June High Dividend Yield -17.3
13 June High EBIT/EV 17.5 June High Forecast PE -16.1
14 June High Asset Backing 16.0 June Large Cap -14.7
15 June Composite Value 14.1 June HS Term Price Mom -12.7
16 June High Dividend Cover 14.0 June Composite Growth -10.8
17 June Mid Cap 8.2 June Low Dividend Cover -9.7
18 June HMT Price Momentum 7.9 June Low Asset Backing -9.4
19 June LST Price Momentum 7.0 June Low EBIT/EV -8.9
20 June Small Cap 3.3 June Low Debt to EV -6.9
21 June Earnings Upgrades 3.1 June LMT Price Momentum -6.0
Say I have to find the number (column C or F) that matches with the
following criterea:
Date: March
Style: High Dividend Yield
If the above mentioned style was only applied in column B, the formula
was very obvious:
=INDEX(C2:C21,MATCH(1,(A2:A21="March")*(ISNUMBER(SEARCH("High Dividen
Yield",B2:B21))),0))
But as you can see for June the style has switched to column E and
therefor I need to retrieve the result fro column F.
Can some one tell me how to extend my current formula!
Many thanx in advance!!!!!!
Regards,
Robert
In a specific sheet I have two ranges of data, overweighted styles and
underweighted styles. From these ranges I would like to retrieve a
result that matches with a date as well as a description of a
particularly style. But as the styles sometimes switch between the two
ranges I have to extend the formula with an IF function, but I have no
clue how to combine this function with a MATCH/INDEX function.
To illustrate my problem please find below a sample:
A B C D E F
1 Date Overweighted Styles Nr Date Underweighted Styles Nr
2 March Low Forecast PE 23.0 March Low Dividend Cover -18.1
3 March High EBIT/EV 17.8 March Low Asset Backing 16.3
4 March Composite Value 16.9 March Composite Growth -15.8
5 March High Dividend Cover 13.7 March Low EBIT/EV -13.8
6 March High Asset Backing 8.0 March High Forecast PE -13.7
7 March High Debt to EV 7.3 March HM Term Price Mom -8.6
8 March Mid Cap 5.3 March High ROIC -7.5
9 March Low ROIC 2.8 March Large Cap -7.2
10 March LMT Price Momentum 2.7 March Earnings Downgrades -7.2
11 March High Dividend Yield 1.7 March Low Debt to EV -5.6
12 June Low Forecast PE 17.9 June High Dividend Yield -17.3
13 June High EBIT/EV 17.5 June High Forecast PE -16.1
14 June High Asset Backing 16.0 June Large Cap -14.7
15 June Composite Value 14.1 June HS Term Price Mom -12.7
16 June High Dividend Cover 14.0 June Composite Growth -10.8
17 June Mid Cap 8.2 June Low Dividend Cover -9.7
18 June HMT Price Momentum 7.9 June Low Asset Backing -9.4
19 June LST Price Momentum 7.0 June Low EBIT/EV -8.9
20 June Small Cap 3.3 June Low Debt to EV -6.9
21 June Earnings Upgrades 3.1 June LMT Price Momentum -6.0
Say I have to find the number (column C or F) that matches with the
following criterea:
Date: March
Style: High Dividend Yield
If the above mentioned style was only applied in column B, the formula
was very obvious:
=INDEX(C2:C21,MATCH(1,(A2:A21="March")*(ISNUMBER(SEARCH("High Dividen
Yield",B2:B21))),0))
But as you can see for June the style has switched to column E and
therefor I need to retrieve the result fro column F.
Can some one tell me how to extend my current formula!
Many thanx in advance!!!!!!
Regards,
Robert
21&E2:E21,0)),INDEX(F2:F21,(MATCH("March"&"High Dividend