Multiple VLOOKUPS

[Buxton1]

Hi,

I'm kind of lost - I am able to do an IF and VLOOKUP to get the required data but now i am trying to add another IF and VLOOKUP formula to multiply by the discount rate if the purchase if made before the discount end date.

I'm really struggling to grasp on the best action to achieve these results.


[TABLE="width: 0"]
<tbody>[TR]
[TD]Options
[/TD]
[TD]Cost
[/TD]
[TD]Maintenance
[/TD]
[/TR]
[TR]
[TD]Phone
[/TD]
[TD]600
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Laptop
[/TD]
[TD]400
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]Desktop
[/TD]
[TD]200
[/TD]
[TD]70
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 0"]
<tbody>[TR]
[TD]People
[/TD]
[TD]Purchase
[/TD]
[TD] Start Date
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]Phone (Dropdown)[/TD]
[TD] 01/01/2017
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]Laptop (Dropdown)
[/TD]
[TD] 24/03/2017
[/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD]Desktop (Dropdown)
[/TD]
[TD]04/04/2017
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 0"]
<tbody>[TR]
[TD]Discount Rate
[/TD]
[TD]20%
[/TD]
[/TR]
[TR]
[TD]Discount End Date
[/TD]
[TD]30/03/2017
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 0"]
<tbody>[TR]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]01/01/2017
[/TD]
[TD]01/02/2017
[/TD]
[TD]01/03/2017
[/TD]
[TD] 01/04/2017
[/TD]
[TD]01/05/2017
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm currently using the formula =IF(C6<C11,VLOOKUP(B6,A5:C8,2,FALSE),0) for BOB 01/01/2017


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

 

[Buxton1]

=IF(C6<c11,vlookup(b6,a5:c8,2,false),0)

<c11,vlookup(b6,a5:c8,2,false),0)< html=""></c11,vlookup(b6,a5:c8,2,false),0)<></c11,vlookup(b6,a5:c8,2,false),0)

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Options	Cost	Maintenance				1/1/2017	1/2/2017	3/24/2017	1/4/2017	4/4/2017
2	Phone	600	30	630		Bob	504	0	0	0	0
3	Laptop	400	50	450		John	0	0	360	0	0
4	Desktop	200	70	270		Andrew	0	0	0	0	270
5
6	People	Purchase	Start Date
7	Bob	Phone	1/1/2017
8	John	Laptop	3/24/2017
9	Andrew	Desktop	4/4/2017
10
11	Discount Rate	20%
12	Discount End Date	3/30/2017
Sheet3

Cell Formulas
Range	Formula
D2	=SUM(B2:C2)
G2	=IF(VLOOKUP($F2,$A$7:$C$9,3,0)=G$1,VLOOKUP(VLOOKUP($F2,$A$7:$C$9,2,0),$A$2:$D$4,4,0))*IF(G$1<=$B$12,1-$B$11,1)

 

[Buxton1]

Hi Sheetspread,

Cost is shown depending on start date

[TABLE="width: 100%"]
<tbody>[TR]
[TD]=IF($D$7<$H$1,VLOOKUP($C$7,$B$2:$C$4,2,0)

Discount Factor applies if within the date range

=IF(G1<=B12,VLOOKUP($C7$7,$B$2:$C$4,2,0)*C11[/TD]
[/TR]
</tbody>[/TABLE]

What i'm looking for is a way to combine both formulas above, so that total cost reflects both start date and discount rate.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D
1	Options	Cost	Maintenance
2	Phone	600	30	630
3	Laptop	400	50	450
4	Desktop	200	70	270
5
6	People	Purchase	Start Date
7	Bob	Phone	1/1/2017	504
8	John	Laptop	3/24/2017	360
9	Andrew	Desktop	4/4/2017	270
10
11	Discount Rate	20%
12	Discount End Date	3/30/2017
Sheet3

Cell Formulas
Range	Formula
D2	=SUM(B2:C2)
D7	=VLOOKUP(B7,$A$2:$D$4,4,0)*(1-IF(C7<=$B$12,$B$11))

 

[Buxton1]

Not quite sure how to display the worksheet correctly. So i have uploaded the spreadsheet to google drive.

https://drive.google.com/file/d/1bSiXl9_dNKUtuoSyIzXZg58RuH3JrjAt/view?usp=sharing

This should give you a more clear view.

Thanks,

 

[Buxton1]

Hi Sheetspread,

I managed to get it to work in the end!

Thanks for the all the help much appreciated!