Forum Discussion
Error Combining Multiple ifs with Logical Test
Good Morning,
I am trying to combine two formulas into a single formula. I need to to source Col. A and based on the value being one of the two, running the formula for the correct brand to source the value needed from another sheet.
Two BASE formulas:
Altria Formula:
=IFS(P4779=1,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J),P4779=2,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*2),P4779=10,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*10))
RJR:
=IFS(P4479=1,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J),P4479=2,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J*2),P4479=10,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$I:$I))
But I need it to look at COL. A and determine the brand to run based on the value.
I had the formula below but it is returning #name because of Logical Test 1 and 2:
=IFS(A4479=1,IFS(P4779=1,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J),P4779=2,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*2),P4779=10,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*10)),A4479=RJR,IFS(P4479=1,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J),P4479=2,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J*2),P4479=10,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$I:$I)))
Im quite stuck on this and have been for a while, any guidance would be much appreciated.
Thank you!
Robert
Hi Robert,
you have in your formula in the second logical test the expression A4479=RJR. The name "RJR" is not known by excel. Therefore you get the #Name error.
=IFS(A4479=1,IFS(P4779=1,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J),P4779=2,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*2),P4779=10,XLOOKUP(E4779,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$E:$E,'[September Buydown & VAPs.xlsx]Altria Buydown File'!$J:$J*10)),A4479=RJR,IFS(P4479=1,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J),P4479=2,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$J:$J*2),P4479=10,XLOOKUP(AC4479,'[September Buydown & VAPs.xlsx]RJR Buydown'!$D:$D,'[September Buydown & VAPs.xlsx]RJR Buydown'!$I:$I)))
If you replace this with e.g. A4479=2 then it should work.- Great thank you, that did work for that instance. Would it be possible to use a NAME rather than a number in A4479? Will Ifs validate a name and not just a number?
- You can compare with a text. Then you have to put this in quotation marks. "RJR". If you want to work with a name from the name editor, then the result of the formula from the name or the fixed content would be compared. So it also goes.
