Jan 23 2022 03:02 PM
=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)
Jan 23 2022 03:07 PM
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)
Jan 24 2022 07:20 AM
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?
Jan 24 2022 07:27 AM
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?
Jan 24 2022 07:46 AM
Jan 24 2022 08:10 AM
Some but not all users see an area "Drag and drop here or browse files to attach" below the area where you compose a reply.
If you see it, simply drag the workbook from Windows Explorer (or Finder) into that area.
If not, you'll have to follow the other suggestion from my previous reply.
Jan 26 2022 06:39 AM
Jan 30 2022 11:48 AM
Jan 30 2022 02:04 PM
Thanks. I see now that the formula tries to return an entire column such as Bgt_FixedExp!D:D or Bgt_Mattress!F:F.
That is not possible of course. Can you explain what the formula should actually return?
Jan 31 2022 06:02 AM
Jan 31 2022 06:55 AM
That doesn't make much sense. See if the attached version does what you want.
Jan 31 2022 07:05 AM
Jan 31 2022 07:30 AM
SolutionTry this then.
Feb 01 2022 09:24 AM
Jan 31 2022 07:30 AM
Solution