Vlookup cross reference two parameters

HNarli

New Member
Joined
Jun 26, 2018
Messages
24
I have a scenario where I am looking up supplier prices in one spreadsheet and putting them into another in order to produce a client quotation. In one tab I have information on our suppliers and their discount structure and on the other I have their prices.

Some of our suppliers offer us discount based on quantities purchased. I am trying to understand therefore if I can do a Vlook up based on two parameters? Hopefully I can explain it!...

Let’s say my supplier is called ‘Ford’ and I was buying 10 cars. From the below you will see I would get 20% discount.

Spreadsheet name = TBL_Supplier_Costs

ABC
SupplierQuantity Break PointDiscount
Ford10%
Ford510%
Ford1020%
Ford2030%
Ford5040%
Vauxhal10%
Vauxhal55%
Vauxhal107%


In my quotation spreadsheet I would like to achieve the following:

XYZAA
SupplierQtySupplier DiscountPrice
Ford10Using cell X look up the supplier in the spreadsheet called [Tbl_Supplier_Costs.xlsx]Qty_Discounts! And using cell Y lookup what the discount would be based on that quantityI’m working this bit out.

I hope someone can understand that! Please let me know if I need to explain anything else. Thank you :)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi HNarli,

Something like this?

Book1
ABCDWXYZAA
1SupplierQuantity Break PointDiscountSupplierQtySupplier DiscountPrice
2Ford10%Ford1020.00%I’m working this bit out.
3Ford510%Ford10.00%
4Ford1020%Ford610.00%
5Ford2030%Ford1020.00%
6Ford5040%Ford5540.00%
7Vauxhal10%Ford00.00%
8Vauxhal55%Ford2730.00%
9Vauxhal107%Ford99940.00%
10Vauxhal20.00%
Qty_Discounts
Cell Formulas
RangeFormula
Z2:Z10Z2=IFERROR(INDEX($C$2:$C$99,AGGREGATE(14,6,ROW($B$2:$B$99)-ROW($C$1)/(($A$2:$A$99=X2)*($B$2:$B$99<=Y2)),1)),0)
 
Upvote 0
My goodness I cannot believe how fast you laid that out and replied - thank you!
The only thing is my supplier list (A,B,C,D) is in a quote spreadsheet, but the W,X,Y,X is in another sheet called [Tbl_Supplier_Costs.xlsx] on the sheet Qty_Discounts - will this still work?
Kind regards
 
Upvote 0
My goodness I cannot believe how fast you laid that out and replied - thank you!
The only thing is my supplier list (A,B,C,D) is in a quote spreadsheet, but the W,X,Y,X is in another sheet called [Tbl_Supplier_Costs.xlsx] on the sheet Qty_Discounts - will this still work?
Kind regards
 
Upvote 0
HNarli,

Apparently so:

Tbl_Supplier_Costs.xlsx
ABC
1SupplierQuantity Break PointDiscount
2Ford10%
3Ford510%
4Ford1020%
5Ford2030%
6Ford5040%
7Vauxhal10%
8Vauxhal55%
9Vauxhal107%
Qty_Discounts


HNarli2.xlsx
XYZAA
1SupplierQtySupplier DiscountPrice
2Ford1020.00%I’m working this bit out.
3Ford10.00%
4Ford610.00%
5Ford1020.00%
6Ford5540.00%
7Ford00.00%
8Ford2730.00%
9Ford99940.00%
10Vauxhal20.00%
Quotation
Cell Formulas
RangeFormula
Z2:Z10Z2=IFERROR(INDEX([Tbl_Supplier_Costs.xlsx]Qty_Discounts!C$2:C$9999,AGGREGATE(14,6,ROW([Tbl_Supplier_Costs.xlsx]Qty_Discounts!B$2:B$9999)-ROW([Tbl_Supplier_Costs.xlsx]Qty_Discounts!C$1)/(([Tbl_Supplier_Costs.xlsx]Qty_Discounts!A$2:A$9999=X2)*([Tbl_Supplier_Costs.xlsx]Qty_Discounts!B$2:B$9999<=Y2)),1)),0)
 
Upvote 0
HNarli,

Apparently so:

Tbl_Supplier_Costs.xlsx
ABC
1SupplierQuantity Break PointDiscount
2Ford10%
3Ford510%
4Ford1020%
5Ford2030%
6Ford5040%
7Vauxhal10%
8Vauxhal55%
9Vauxhal107%
Qty_Discounts


HNarli2.xlsx
XYZAA
1SupplierQtySupplier DiscountPrice
2Ford1020.00%I’m working this bit out.
3Ford10.00%
4Ford610.00%
5Ford1020.00%
6Ford5540.00%
7Ford00.00%
8Ford2730.00%
9Ford99940.00%
10Vauxhal20.00%
Quotation
Cell Formulas
RangeFormula
Z2:Z10Z2=IFERROR(INDEX([Tbl_Supplier_Costs.xlsx]Qty_Discounts!C$2:C$9999,AGGREGATE(14,6,ROW([Tbl_Supplier_Costs.xlsx]Qty_Discounts!B$2:B$9999)-ROW([Tbl_Supplier_Costs.xlsx]Qty_Discounts!C$1)/(([Tbl_Supplier_Costs.xlsx]Qty_Discounts!A$2:A$9999=X2)*([Tbl_Supplier_Costs.xlsx]Qty_Discounts!B$2:B$9999<=Y2)),1)),0)


Oh my, you are AMAZING!!! I cannot thank you enough for taking the time to help me with this. I'm really grateful and it's certainly saved me a lot of head scratching. Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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