Hello,
I am trying to write a multi-function / multiple criteria formula and have tried both index match & xlookup but cannot get the formula to work. If I break the formula down into component parts then each component part seems to work but put these sections together into one formula and it will not work.
Referring to my uploaded image:
1) First part of the formula needs to check the value in col. C (fixed rate) if it is greater than the corresponding value in col. L (for both year & code combined). If it is greater then output in col. C needs to say text: "Fixed rate".
2) If the value in col. C is less than the corresponding value in col. L then the formula needs to be =(Corresponding value in col. L minus col. C) / corresponding value in col. L to give the answer as a % in col. B.
3) I have tried the following formulas but none are working:
=IF(C3>INDEX(L3:L24,MATCH(1,(I3:I24=A3)*(J3:J24=D3),0)),"£"&C3&" Fixed rate")*(XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)-C3)/XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24) gives me an answer of 0.00% but needs to be 38.00%. Note that there should be a "," not a "*" between "Fixed Rate" & (XLOOKUP....). Each time I type a comma then press enter, Excel changes the comma to a "*" in the formula.
{=IF(C3>INDEX(L3:L24,MATCH(1,(I3:I24=A3)*(J3:J24=D3),0)),"£"&C3&" Fixed rate")*(XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)-C3)/XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)} same answer as above.
=IF(C4>XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24),"Fixed Rate")*((XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24-C4)/XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24))) but again, Excel converts the "," to a "*" and the value brings back an error.
I have tried one or two other permutations of these formulas but cannot get it to work.
Can anyone please suggest an answer even if it means using a different formula. Thanks in advance.
I am trying to write a multi-function / multiple criteria formula and have tried both index match & xlookup but cannot get the formula to work. If I break the formula down into component parts then each component part seems to work but put these sections together into one formula and it will not work.
Referring to my uploaded image:
1) First part of the formula needs to check the value in col. C (fixed rate) if it is greater than the corresponding value in col. L (for both year & code combined). If it is greater then output in col. C needs to say text: "Fixed rate".
2) If the value in col. C is less than the corresponding value in col. L then the formula needs to be =(Corresponding value in col. L minus col. C) / corresponding value in col. L to give the answer as a % in col. B.
3) I have tried the following formulas but none are working:
=IF(C3>INDEX(L3:L24,MATCH(1,(I3:I24=A3)*(J3:J24=D3),0)),"£"&C3&" Fixed rate")*(XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)-C3)/XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24) gives me an answer of 0.00% but needs to be 38.00%. Note that there should be a "," not a "*" between "Fixed Rate" & (XLOOKUP....). Each time I type a comma then press enter, Excel changes the comma to a "*" in the formula.
{=IF(C3>INDEX(L3:L24,MATCH(1,(I3:I24=A3)*(J3:J24=D3),0)),"£"&C3&" Fixed rate")*(XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)-C3)/XLOOKUP(1,(I3:I24=A3)*(J3:J24=D3),L3:L24)} same answer as above.
=IF(C4>XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24),"Fixed Rate")*((XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24-C4)/XLOOKUP(1,(I3:I24=A4)*(J3:J24=D4),L3:L24))) but again, Excel converts the "," to a "*" and the value brings back an error.
I have tried one or two other permutations of these formulas but cannot get it to work.
Can anyone please suggest an answer even if it means using a different formula. Thanks in advance.
