Formula with nested IF statements, VLOOKUPs, and IFERROR statements - error message

%3CLINGO-SUB%20id%3D%22lingo-sub-3367266%22%20slang%3D%22en-US%22%3EFormula%20with%20nested%20IF%20statements%2C%20VLOOKUPs%2C%20and%20IFERROR%20statements%20-%20error%20message%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367266%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20price%20column%20that%20will%20bring%20in%20pricing%20information%20from%20a%20price%20sheet.%20There%20are%20six%20different%20vendors%20(I%20am%20creating%20one%20IF%20statement%20for%20each%20vendor)%20and%20the%20correct%20value%20for%20%22ModelFamily%22%20can%20be%20in%20three%20different%20columns%20which%20is%20why%20I%20have%20so%20many%20IFERROR%20functions.%3C%2FP%3E%3CP%3EThe%20function%20below%20works%20perfectly%2C%20however%20when%20I%20try%20to%20add%20another%20IF%20statement%20I%20get%20the%20following%20error%3A%20%22You%20entered%20too%20many%20arguments%20for%20this%20function%22.%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20what%20could%20be%20wrong%3F%20Any%20help%20would%20be%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EWorking%20function%3A%3C%2FP%3E%3CP%3E%3DIF(%5B%40Vendor%5D%20%3D%20%22Vendor1%22%2C%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24D%3A%24X%2C21%2CFALSE)%2CIFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24E%3A%24X%2C20%2CFALSE)%2CVLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24C%3A%24X%2C22%2C%20FALSE))))%3C%2FP%3E%3CP%3EError%20function%3A%3C%2FP%3E%3CP%3E%3DIF(%5B%40Vendor%5D%20%3D%20%22Vendor1%22%2C%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24D%3A%24X%2C21%2CFALSE)%2CIFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24E%3A%24X%2C20%2CFALSE)%2CVLOOKUP(%5B%40ModelFamily%5D%2C'%5BBulk%20-%20Master%20Tracker.xlsx%5DPriceList'!%24C%3A%24X%2C22%2C%20FALSE)%2CIF(%5B%40Vendor%5D%20%3D%20%22Vendor2%22%2C%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24D%3A%24Y%2C22%2CFALSE)%2CIFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24E%3A%24Y%2C21%2CFALSE)%2CVLOOKUP(%5B%40ModelFamily%5D%2C'pricesheet.xlsx%5DPriceList'!%24C%3A%24Y%2C23%2C%20FALSE))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3367266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3367364%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20nested%20IF%20statements%2C%20VLOOKUPs%2C%20and%20IFERROR%20statements%20-%20error%20message%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388838%22%20target%3D%22_blank%22%3E%40Vexum_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20it's%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%0AIF(%20%5B%40Vendor%5D%20%3D%20%22Vendor1%22%2C%0A%20%20%20%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24D%3A%24X%2C21%2CFALSE)%2C%0A%20%20%20%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24E%3A%24X%2C20%2CFALSE)%2C%0A%20%20%20%20VLOOKUP(%5B%40ModelFamily%5D%2C'%5BBulk%20-%20Master%20Tracker.xlsx%5DPriceList'!%24C%3A%24X%2C22%2C%20FALSE)))%2C%0AIF(%20%5B%40Vendor%5D%20%3D%20%22Vendor2%22%2C%0A%20%20%20%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24D%3A%24Y%2C22%2CFALSE)%2C%0A%20%20%20%20IFERROR(VLOOKUP(%5B%40ModelFamily%5D%2C'%5Bpricesheet.xlsx%5DPriceList'!%24E%3A%24Y%2C21%2CFALSE)%2C%0A%20%20%20%20VLOOKUP(%5B%40ModelFamily%5D%2C'pricesheet.xlsx%5DPriceList'!%24C%3A%24Y%2C23%2C%20FALSE)))%20))%20%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to create a price column that will bring in pricing information from a price sheet. There are six different vendors (I am creating one IF statement for each vendor) and the correct value for "ModelFamily" can be in three different columns which is why I have so many IFERROR functions.

The function below works perfectly, however when I try to add another IF statement I get the following error: "You entered too many arguments for this function".

Does anyone know what could be wrong? Any help would be appreciated

Working function:

=IF([@Vendor] = "Vendor1", IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$D:$X,21,FALSE),IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$E:$X,20,FALSE),VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$C:$X,22, FALSE))))

Error function:

=IF([@Vendor] = "Vendor1", IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$D:$X,21,FALSE),IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$E:$X,20,FALSE),VLOOKUP([@ModelFamily],'[Bulk - Master Tracker.xlsx]PriceList'!$C:$X,22, FALSE),IF([@Vendor] = "Vendor2", IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$D:$Y,22,FALSE),IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$E:$Y,21,FALSE),VLOOKUP([@ModelFamily],'pricesheet.xlsx]PriceList'!$C:$Y,23, FALSE))))))

1 Reply

@Vexum_ 

I guess it's like

=
IF( [@Vendor] = "Vendor1",
    IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$D:$X,21,FALSE),
    IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$E:$X,20,FALSE),
    VLOOKUP([@ModelFamily],'[Bulk - Master Tracker.xlsx]PriceList'!$C:$X,22, FALSE))),
IF( [@Vendor] = "Vendor2",
    IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$D:$Y,22,FALSE),
    IFERROR(VLOOKUP([@ModelFamily],'[pricesheet.xlsx]PriceList'!$E:$Y,21,FALSE),
    VLOOKUP([@ModelFamily],'pricesheet.xlsx]PriceList'!$C:$Y,23, FALSE))) ))