SOLVED

Can someone tell me why I keep getting a naming error on this formula?

Copper Contributor

=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)

13 Replies

@rachel1205 

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)

@Hans Vogelaar 

 

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?

@rachel1205 

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?

How do I attach a sample workbook?

@rachel1205 

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.

Hi Hans,

I will get this to you via One Drive this weekend.

Thank you for all of your help.
Hans, here is the link to my excel file with the value problem. I put a Tab labelled Instructions to help you understand what I am doing. https://1drv.ms/x/s!AgXFzJ74gEObam1YmvJaHILI_0I?e=YfdneS

@rachel1205 

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?

It should return the actual value in the column.

@rachel1205 

That doesn't make much sense. See if the attached version does what you want.

No, it does not work. Row 5 should return a value of -16083, which is from column D in the Bgt_FixedExp tab and Row 20 should return a value of -7500, which is from column D in the Bgt_FixedExp tab also. All other values for column 2-21 should be 0. I hope this helps.
best response confirmed by rachel1205 (Copper Contributor)
Solution

@rachel1205 

Try this then.

It worked. Thanks a bunch for your help.
1 best response

Accepted Solutions
best response confirmed by rachel1205 (Copper Contributor)