What I need to do here is take the vlookup and separate into 2 separate price points. Price Point 1 would return in columns E:J, then Price Point 2 would return in columns L:K. I've attache the 2 sheets that I'm working with.

I have the formula:=IFERROR(VLOOKUP($B$2&":"&D3,'Picklist by Individual Unit Qty'!B1:K8603,5,FALSE),"")

BUT, I need to return the value only if column K on the lookup sheet matches a specific criteria which appears in cell A13 of Destination sheet. Any Ideas

Attached Images

	Capture.PNG (74.9 KB, 23 views)
	Destination Sheet.PNG (80.5 KB, 23 views)

If you name your ranges in column B, F and K in Picklist:

Helper
Container.Name and
Price.point

then try the following array formula:

=IFERROR(INDEX(Container.Name,MATCH($B$2&":"&D3&$A $13,Helper&Price.Point,0)),"")

Please note that there shouldn't be a space between A and $13.

This error is a MSOfficeForums thing.

I think for you will work:

1. Rename column headings so they are without spaces (it's optional)
Container.Name > Container
Lot.ID/Lot_ID > Lot
Price Point > PricePoint

2. Define names
nStore = DestinationSheet!$B$2
nPricePoint = DestinationSheet!$A$13

3. Define both tables on sheet 'Picklist By Individual Unit Qty' and 'DestinationSheet' as Tables, p.e. tSource and tDestination

The table formula will be probably (I'm not fully sure how did you calculate row numbers in column B of source sheet)

In case you don't want to use Table formula, you have to replace all column references with absolute references, row references with dynamic references [and names with absolute references].

Arvi I don't need sums of anything, I need to create 2 separate Pick list for 1 store from 1 huge pick list of multiple stores and 2 price points

Yes, but what about the suggestions in post #2 and 3?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

When you calculate a sum which returns a numeric value from certain row, it works effectively as lookup. The idea is to compose conditions for sumifs() in a way, that a single numeric value is returned. I myself use this technique often, as it allows to apply unlimited number of conditions.

It would be so much easier to provide a useful answer if you uploaded your actual project instead of some more or less useless pictures and gave examples of expected results.