Forum Discussion
Can someone tell me why I keep getting a naming error on this formula?
=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=FIXED EXPENSES,R2=308-0),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=MATTRESS Revenue,R2=101-1),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Pillow Revenue,R2=111-1),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Other Revenue,R2=120-1),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Discounts,R2=190-4),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Variable EXPENSES,R2=201-1),Bgt_VarExp!K:K,0)
Try this then.
13 Replies
Literal text values must be enclosed in straight double quotes " ":
=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="FIXED EXPENSES",R2=308-0),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="MATTRESS Revenue",R2=101-1),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Pillow Revenue",R2=111-1),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Other Revenue",R2=120-1),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Discounts",R2=190-4),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Variable EXPENSES",R2=201-1),Bgt_VarExp!K:K,0)
If 308-0 etc. are text values too, instead of calculations:
=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="FIXED EXPENSES",R2="308-0"),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="MATTRESS Revenue",R2="101-1"),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Pillow Revenue",R2="111-1"),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Other Revenue",R2="120-1"),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Discounts",R2="190-4"),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Variable EXPENSES",R2="201-1"),Bgt_VarExp!K:K,0)
Thank you for the tip, however, now I am getting a Value error. I did as you suggested, and put double quotes around Fixed Expenses etc... and the 308-0 etc..
Any idea why I am getting a value error now?
Could you attach a sample workbook demonstrating the problem, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
