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.

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>

 

[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
1	Reference ID	Product Name	Start Date	End Date	Price
2	123	Product ABCD	10/1/2016	10/31/2016	$59.00
3
4	Reference ID	Product Name	Price	Start Date	End Date
5	123	Product ABCD	182	7/1/2018	6/30/2019
6	124	Product EFGH	75	7/1/2015	6/30/2017
7	124	Product EFGH	64	7/1/2017	6/30/2018
8	124	Product EFGH	87	7/1/2018	6/30/2019
9	123	Product ABCD	59	7/1/2015	6/30/2017
10	123	Product ABCD	24	7/1/2017	6/30/2018
11

<tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula 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]

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.