Nested VLOOKUP in an IF statement

[jlcochran38]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference ID[/TD]
[TD]Product Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[TD]Product ABCD[/TD]
[TD]10/1/2016[/TD]
[TD]10/31/2016[/TD]
[TD]$59.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference ID[/TD]
[TD]Product Name[/TD]
[TD]Price[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[TD]Product ABCD[/TD]
[TD]182.00[/TD]
[TD]7/1/2018[/TD]
[TD]6/30/2019[/TD]
[/TR]
[TR]
[TD]00124[/TD]
[TD]Product EFGH[/TD]
[TD]75.00[/TD]
[TD]7/1/2015[/TD]
[TD]6/30/2017[/TD]
[/TR]
[TR]
[TD]00124[/TD]
[TD]Product EFGH[/TD]
[TD]64.00[/TD]
[TD]7/1/2017[/TD]
[TD]6/30/2018[/TD]
[/TR]
[TR]
[TD]00124[/TD]
[TD]Product EFGH[/TD]
[TD]87.00[/TD]
[TD]7/1/2018[/TD]
[TD]6/30/2019[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[TD]Product ABCD[/TD]
[TD]59.00[/TD]
[TD]7/1/2015[/TD]
[TD]6/30/2017[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[TD]Product ABCD[/TD]
[TD]24.00[/TD]
[TD]7/1/2017[/TD]
[TD]6/30/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

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

	A	B	C	D	E	F	G
Reference ID	Product Name	Start Date	End Date	Price
Product ABCD
Reference ID	Product Name	Price	Start Date	End Date
Product ABCD
Product EFGH
Product EFGH
Product EFGH
Product ABCD
Product ABCD

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]10/31/2016[/TD]
[TD="align: right"]$59.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]182[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]124[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]124[/TD]

[TD="align: right"]64[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]124[/TD]

[TD="align: right"]87[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUMIFS($C$5:$C$10,$A$5:$A$10,A2,$D$5:$D$10,"<="&C2,$E$5:$E$10,">="&D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jlcochran38]

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]

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]

It just returns a $0.00

 

[Eric W]

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]

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.