Hello,
I've been trying to get my head around, where the "logical" broken link is within the spreadsheet.
Purpose behind it is depending on the List code will determine the formula's behaviour, whether it'll be charged at London rate, or Non-London rate, depending upon the dates and at which point the dates sit.
I can get the formula to work for one or the other separately but trying to combined them is causing a N/A error. What am I not seeing?
=IF(MATCH($V$1,T17:T22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,2,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,2,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,2,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,2,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,2,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,2,0),
IF(MATCH($V$1,U17:U22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,4,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,4,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,4,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,4,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,4,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,4,0),))))))))))))))
The first part is to calculate the rate for Non-London within the tables/date dependant and will calculate only if the ID number is within the Non-London list,(column T). It'll also work for column U with the London rates but unable to combined the pair.
Much appreciate.
I've been trying to get my head around, where the "logical" broken link is within the spreadsheet.
Purpose behind it is depending on the List code will determine the formula's behaviour, whether it'll be charged at London rate, or Non-London rate, depending upon the dates and at which point the dates sit.
I can get the formula to work for one or the other separately but trying to combined them is causing a N/A error. What am I not seeing?
=IF(MATCH($V$1,T17:T22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,2,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,2,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,2,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,2,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,2,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,2,0),
IF(MATCH($V$1,U17:U22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,4,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,4,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,4,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,4,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,4,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,4,0),))))))))))))))
The first part is to calculate the rate for Non-London within the tables/date dependant and will calculate only if the ID number is within the Non-London list,(column T). It'll also work for column U with the London rates but unable to combined the pair.
Much appreciate.
Attachments
Last edited: