Multiple VLOOKUPS

Buxton1

New Member
Joined
Mar 25, 2018
Messages
5
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,
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
=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)
 
Upvote 0
This?


Excel 2010
ABCDEFGHIJK
1OptionsCostMaintenance1/1/20171/2/20173/24/20171/4/20174/4/2017
2Phone60030630Bob5040000
3Laptop40050450John0036000
4Desktop20070270Andrew0000270
5
6PeoplePurchaseStart Date
7BobPhone1/1/2017
8JohnLaptop3/24/2017
9AndrewDesktop4/4/2017
10
11Discount Rate20%
12Discount End Date3/30/2017
Sheet3
Cell Formulas
RangeFormula
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)
 
Upvote 0
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.
 
Upvote 0
This?


Excel 2010
ABCD
1OptionsCostMaintenance
2Phone60030630
3Laptop40050450
4Desktop20070270
5
6PeoplePurchaseStart Date
7BobPhone1/1/2017504
8JohnLaptop3/24/2017360
9AndrewDesktop4/4/2017270
10
11Discount Rate20%
12Discount End Date3/30/2017
Sheet3
Cell Formulas
RangeFormula
D2=SUM(B2:C2)
D7=VLOOKUP(B7,$A$2:$D$4,4,0)*(1-IF(C7<=$B$12,$B$11))
 
Upvote 0
Hi Sheetspread,

I managed to get it to work in the end!

Thanks for the all the help much appreciated!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top