Forum Discussion
Complex IF Formula with multiple
Hi, I am new to Excel and trying to complete what I would consider quite a complicated IF formula (if that is even the correct function to use here!)
An example of my Dataset:
| A | B | C | D | E | F |
| 1 | FUND | FUND CODE | TYPE | TYPE CODE | RATE |
| 2 | Fund1 | 1 | Cat | 1 | |
| 3 | Fund1 | 1 | Dog | 2 | |
| 4 | Fund1 | 1 | Fish | 3 | |
| 5 | Fund2 | 2 | Cat | 1 | |
| 6 | Fund2 | 2 | Dog | 2 |
In a separate sheet I have the Rate Table:
| Fund 1 | Cat | 0.10 |
| Fund 1 | Dog | 0.20 |
| Fund 1 | Fish | 0.30 |
| Fund 2 | Cat | 0.40 |
| Fund 2 | Dog | 0.50 |
| Fund 2 | Fish | 0.60 |
In F, I want to say IF B2 = Fund1 and D2 = Cat = 0.10 (from the rate table) and so on.
Please can you help with the formula to use? Note, I have create the FUND CODE and TYPE CODE thinking that it would simplify the formula needed for F, also not sure if this is the best approach!
Also may be worth noting that in my real dataset I have 11+ Types!
4 Replies
At least couple of variants
1)
=LOOKUP(2,1/($J$2:$J$7=$B4)/($K$2:$K$7=$D4)*$L$2:$L$7)2)
=INDEX($L$2:$L$7,MATCH(1,INDEX(($J$2:$J$7=$B4)*($K$2:$K$7=$D4),0),0))Thanks for the quick reply SergeiBaklan
Quick question, of the 2 options you've provided, which would be more suitable if the rates were changing quarterly? Also, in my example the cat/dog/fish were the same in both funds, what if there are different types for each fund? E.g. fund 1 = cat, dog fish however fund 1 = owl, cow, horse
would there be a more favourable formula of the 2 options you've provided?
Better the formula with which you are more familiar, I personally prefer INDEX/MATCH.
Your lookup the combination of fund and type, i.e. (FUND=fund1) AND (TYPE=type1), that doesn't matter which exactly types are.
