double vlookup help please

[tenone60]

Hello,

Can anyone suggest a formula for the cells shaded in yellow in my screen shot? The answers I expect from the formula are also listed below in range C8:C11. Thank you.

Unknown
	A	B	C	D	E	F	G	H
1	Product Code	Date Sold	Selling Price			Product Code	Price Effective Date	Price
2	123	1/15/2016				123	12/1/2015	$ 125.00
3	123	3/1/2016				123	3/1/2016	$ 130.00
4	456	2/25/2016				456	1/1/2016	$ 215.00
5	456	4/8/2016				456	4/15/2016	$ 245.00
6
7			answers s/b
8			$ 125.00
9			$ 130.00
10			$ 215.00
11			$ 215.00
Sheet1

Cell Formulas
Range	Formula
A1	Product Code
A2	123
A3	123
A4	456
A5	456
B1	Date Sold
B2	42384
B3	42430
B4	42425
B5	42468
C1	Selling Price
C7	answers s/b
C8	125
C9	130
C10	215
C11	215
F1	Product Code
F2	123
F3	123
F4	456
F5	456
G1	Price Effective Date
G2	42339
G3	42430
G4	42370
G5	42475
H1	Price
H2	125
H3	130
H4	215
H5	245

 

[Nishant94]

Important: As this is an array formula you need to press "Ctrl+Shift+Enter" after entering the formula in the cell.

Excel 2016
	A	B	C	D	E	F	G	H
1	Product Code	Date Sold	Selling Price			Product Code	Price Effective Date	Price
2	123	01/15/2016	$ 125.00			123	12/01/2015	$ 125.00
3	123	03/01/2016	$ 130.00			123	03/01/2016	$ 130.00
4	456	02/25/2016	$ 215.00			456	01/01/2016	$ 215.00
5	456	04/08/2016	$ 215.00			456	04/15/2016	$ 245.00
6
Sheet3

Cell Formulas
Range	Formula
C2	=INDEX($H$2:$H$5,MATCH(B2*A2,$G$2:$G$5*$F$2:$F$5,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=MIN(IF($F$2:$F$6=$A2,IF($G$2:$G$6<=$B2,$H$2:$H$6)))

With some control...

=IFERROR(1/(1/MIN(IF($F$2:$F$6=$A2,IF($G$2:$G$6<=$B2,$H$2:$H$6)))),"")

 

[tenone60]

Thanks! The formula returned the correct answer in all cells except C3. Could it be that the portion of the formula where G2:G5 <=B3 is deemed “true” for both dates 12/1/15 and 3/1/16 so the answer defaults to the first “true” it encounters or does the answer default to the MIN price when there are two “true” possibilities? Thanks again!

Unknown
	A	B	C	D	E	F	G	H
1	Product Code	Date Sold	Selling Price			Product Code	Price Effective Date	Price
2	123	1/15/2016	125			123	12/1/2015	$ 125.00
3	123	3/1/2016	125			123	3/1/2016	$ 130.00
4	456	2/25/2016	215			456	1/1/2016	$ 215.00
5	456	4/8/2016	215			456	4/15/2016	$ 245.00
Sheet1

Cell Formulas
Range	Formula
C2	{=MIN(IF($F$2:$F$5=A2,IF($G$2:$G$5<=B2,$H$2:$H$5)))}
C3	{=MIN(IF($F$2:$F$5=A3,IF($G$2:$G$5<=B3,$H$2:$H$5)))}
C4	{=MIN(IF($F$2:$F$5=A4,IF($G$2:$G$5<=B4,$H$2:$H$5)))}
C5	{=MIN(IF($F$2:$F$5=A5,IF($G$2:$G$5<=B5,$H$2:$H$5)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tenone60]

This worked perfectly but I confess, I don't understand it, especially the part that follows MATCH(A2*B2... Can you explain how it works? Thanks for the formula.

 

[Aladin Akyurek]

Is price effective date always productwise in ascending order? That is, the following is impossible?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

2​

[/td][td]

123​

[/td][td]

3/1/2016​

[/td][td]

$130.00 ​

[/td][/tr]


[tr][td]

3​

[/td][td]

123​

[/td][td]

12/1/2015​

[/td][td]

$125.00 ​

[/td][/tr]
[/table]

 

[Aladin Akyurek]

Thanks! The formula returned the correct answer in all cells except C3. Could it be that the portion of the formula where G2:G5 <=B3 is deemed “true” for both dates 12/1/15 and 3/1/16 so the answer defaults to the first “true” it encounters or does the answer default to the MIN price when there are two “true” possibilities? Thanks again!
Unknown

	A	B	C	D	E	F	G	H
Product Code	Date Sold	Selling Price	Product Code	Price Effective Date	Price

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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"]1/15/2016[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"] $ 125.00 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]3/1/2016[/TD]
[TD="bgcolor: #FF99FF, align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]3/1/2016[/TD]
[TD="align: right"] $ 130.00 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]2/25/2016[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"] $ 215.00 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]4/8/2016[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]4/15/2016[/TD]
[TD="align: right"] $ 245.00 [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=MIN(IF($F$2:$F$5=A2,IF($G$2:$G$5<=B2,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=MIN(IF($F$2:$F$5=A3,IF($G$2:$G$5<=B3,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=MIN(IF($F$2:$F$5=A4,IF($G$2:$G$5<=B4,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=MIN(IF($F$2:$F$5=A5,IF($G$2:$G$5<=B5,$H$2:$H$5)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter, not just enter, and copy down:

=SMALL(IF($F$2:$F$5=$A2,IF($G$2:$G$5<=$B2,$H$2:$H$5)),MIN(COUNTIFS($A$2:A2,A2),MAX(FREQUENCY(IF($A$2:$A$5=A2,$B$2:$B$5),$G$2:$G$5))))

which is appears to be robust.

 

[tenone60]

Should I sort my data in columns F-H in a certain order? On 12/1/15, my selling price for Product 123 was$125 but on 3/1/16, I raised my price on Product 123 to $130.