# Complex If and Nested Vlookup formulas

#### kingstreasure

##### New Member
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)

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

Replies
8
Views
340
Replies
0
Views
237
Replies
3
Views
109
Replies
7
Views
161
Replies
18
Views
380

1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back