Error Combining Multiple ifs with Logical Test

[RwadeEM]

Spoiler

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

[#2]

@RwadeEM 

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.

[#3]

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?

[#4]

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.