# Nested VLOOKUP in an IF statement

#### jlcochran38

##### New Member
I need some help with a formula (see tables below). What I need is for the Reference ID in the first table to lookup the Reference ID in the second table, and if the date range for that Reference ID in the first table falls inside one the date ranges within the second table, the price needs to show.

 Reference ID Product Name Start Date End Date Price 00123 Product ABCD 10/1/2016 10/31/2016 \$59.00

<tbody>
</tbody>

 Reference ID Product Name Price Start Date End Date 00123 Product ABCD 182.00 7/1/2018 6/30/2019 00124 Product EFGH 75.00 7/1/2015 6/30/2017 00124 Product EFGH 64.00 7/1/2017 6/30/2018 00124 Product EFGH 87.00 7/1/2018 6/30/2019 00123 Product ABCD 59.00 7/1/2015 6/30/2017 00123 Product ABCD 24.00 7/1/2017 6/30/2018

<tbody>
</tbody>

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Eric W

##### MrExcel MVP
If you're sure you won't have multiple prices in the same range, you can use a SUMIFS:

ABCDEFG
1Reference IDProduct NameStart DateEnd DatePrice
2123Product ABCD10/1/201610/31/2016\$59.00
3
4Reference IDProduct NamePriceStart DateEnd Date
5123Product ABCD1827/1/20186/30/2019
6124Product EFGH757/1/20156/30/2017
7124Product EFGH647/1/20176/30/2018
8124Product EFGH877/1/20186/30/2019
9123Product ABCD597/1/20156/30/2017
10123Product ABCD247/1/20176/30/2018
11

<tbody>
</tbody>
Sheet21

Worksheet Formulas
CellFormula
E2=SUMIFS(\$C\$5:\$C\$10,\$A\$5:\$A\$10,A2,\$D\$5:\$D\$10,"<="&C2,\$E\$5:\$E\$10,">="&D2)

<tbody>
</tbody>

<tbody>
</tbody>

#### jlcochran38

##### New Member
Your formula works fine, but when I try to transcribe it into my spreadsheet (changing the reference locations) it doesn't seem to work anymore.

=SUMIFS('Contract Pricing All'!\$C\$2:\$C\$7,'Contract Pricing All'!\$A\$2:\$A\$7,Recovered_Sheet1!A2,'Contract Pricing All'!\$D\$2:\$D\$7,"<="&Recovered_Sheet1!C2,'Contract Pricing All'!\$E\$2:\$E\$7,">="&Recovered_Sheet1!D2)

#### Eric W

##### MrExcel MVP
When you say "doesn't seem to work" what do you mean? Returns an error, or zero value, or wrong value?

I renamed the sheets in my test workbook to match your formula, then pasted it exactly as you wrote it, and I got the right response. Just make sure that the ranges are correct, and the sheet names are too (spaces vs. underlines).

#### jlcochran38

##### New Member
It just returns a \$0.00

#### Eric W

##### MrExcel MVP
I'm not sure what else to tell you. It works fine in my workbook, even using the formula you provided in post 3. Make sure that your ranges are correct, make sure that the dates are actual dates, not text, and try again.

#### jlcochran38

##### New Member
It looks like the latter is the issue. This is not my spreadsheet, but whoever used it last used some jacked up formatting on the dates...some even having spacing. That's probably the issue.

Replies
4
Views
176
Replies
1
Views
206
Replies
5
Views
205
Replies
4
Views
276
Replies
2
Views
236

1,195,640
Messages
6,010,876
Members
441,571
Latest member
stolenweasel

### 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