Help with Spill Error

[Lorel_Siemens]

I would like to turn off the Dynamic Array function so that I do not get the Spill Error.  Is this possible?  I am trying to do an impact analysis.  I want to VLookup values into my file.  I am working with 75 price groups that can have 1 of 3 discounts.  My impact analysis is based on sales, so the customers are not grouped in this file.  I use concat to put the data together before I do the Vlookup.  Before I would filter on Discount 1 and then use VLookup to pull in the discounts for those discount 1 customers.  Now if I do that, I will get the spill error when I copy and paste the values for the Discount 1 customers and try to move on to the Discount 2 customers.  Any ideas?  

CONCAT PrGp / MG3 / BPC	Disct	SOLD QTY	EXT LIST PRICE	EXT NET PRICE	Multiplier	Discount	AUNP	SUM IF	New MG3 Disct for XXX	New Net Sales
CC IN3ISWITCH PROGRAM	1	1	1,525.00	219.60	0.1440	85.60	219.60	219.60
CC IN3HSWITCH PROGRAM	1	4	1,828.00	263.24	0.1440	85.60	65.81	263.24

[#2]

Hi @Lorel_Siemens 

add "@" before the formula and make it as Implicit Intersection.

copy of sample file with your constructed formula will be helpful 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

[#3]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#4]

Hello,

You can't actually turn off the Dynamic Array spill... The spill error occured basically because something obstructed the spill range which you can easy handle... For example, your spill range is A1:A10... If there is value in, for example,cell A5, this would return spill error... All you need to do is remove the value in A5.

[#5]

@Abiola1 Thanks for the suggestion, except that I want to VLookup into the same column three times (one time for each discount).  I think I have figured out a solution for my problem.  I will just add three more columns into my file - one column for each discount group.