Complex If and Nested Vlookup formulas

[kingstreasure]

Hi

Hopefully I explained this correctly so that someone understands my madness.

I have an excel worksheet that contains IF and nested VLOOKUP formulas throughout numerous cells in a tab named SummaryWk. That sheet is populated with data from the SummaryData tab, based on the delivery date the quantities are updated in the appropriate weekly bucket (similar to a pivot table) in the SummaryWk tab. The formula works for the most part, but for some reason if the error statement is true it doesn't return a value under the weekly buckets.


SummaryData - *I have other columns in between, I just wanted to show the main fields with the column names for the formula since its complex.

	A	F	L	U
1	JoinKey	Tracking#	Delivery Date	Quantity
2	ABCDEFG123456	22233333	11/20/2014	434,556
3	ABCDEFG122223	12343211	11/9/2014	42,444
4	ABCDEFG124444	44411234	11/23/2014	333,442
5	ABCDEFG124111	23345666	11/16/2014	1,223

<tbody>
</tbody>

SummaryWk - *depending on the accounting period columns U through X will at times not have data, that is why W and X show "#VALUE!"

	A	F	U	V	W	X	Y	Z
1	JoinKey	Tracking#	11/9/2014	11/16/2014	#VALUE!	#VALUE!	11/23/2014	12/23/2014
2	ABCDEFG123456	22233333
3	ABCDEFG122223	12343211	42,444
4	ABCDEFG124444	44411234					333,442
5	ABCDEFG124111	23345666	1,223

<tbody>
</tbody>

I'm not sure what needs to be changed within my formula:

Cell U2 = IF(AND(ISERROR($U$1), ISERROR($V$1)), 0, IF(AND(ISERROR($V$1),VLOOKUP(A2,SummaryData!$A$2:$U$3206,12,FALSE)>=$U$1,VLOOKUP(A2,SummaryData!$A$2:$U$3206,12,FALSE)<$Y$1),VLOOKUP(A2,SummaryData!$A$1:$U$3206,21,FALSE),IF(ISERROR($V$1),0,IF(AND(VLOOKUP(A2,SummaryData!$A$2:$U$3206,12,FALSE)>=$U$1,VLOOKUP(A2,SummaryData!$A$2:$U$3206,12,FALSE)<$V$1),VLOOKUP(A2,SummaryData!$A$2:$U$3206,21,FALSE),0))))

The above formula is used in cells V2 and W2

Cell X2 =IF(ISERROR($X$1), 0, IF(AND(VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)>=$X$1, VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)<$Y$1), VLOOKUP(A2,SummaryData!$A$2:$U$4965,21,FALSE),0))

Cell Y2 = IF(AND(VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)>=$Y$1, VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)<$Z$1),VLOOKUP(A2,SummaryData!$A$2:$U$4965,21,FALSE),0)

Cell Z2 =IF(AND(VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)>=$Z$1, VLOOKUP(A2,SummaryData!$A$2:$U$4965,12,FALSE)<$AA$1),VLOOKUP(A2,SummaryData!$A$2:$U$4965,21,FALSE),0)

 

[xpluto]

It looks like you are trying to extract data from "SummaryData " into SheeSummaryWk - cell that matches the "JoinKey", "Tracking#" and the date?

If this is correct, here's a simpler formula:
=SUMPRODUCT(--($A2='SummaryData '!$A:$A),--(SheeSummaryWk!$B2='SummaryData '!$B:$B),--(SheeSummaryWk!C$1='SummaryData '!$C:$C),'SummaryData '!$U:$U)

this formula was used in C2 in the "SheeSummaryWk" workbook, and you can copy and paste across your table.

<tbody>
</tbody>

here the file i mocked:
https://www.dropbox.com/s/v7es4of22uiou7o/Book1.xlsx?dl=0

hope this helps.

V