Vlookup cross reference two parameters

HNarli

New Member
Joined
Jun 26, 2018
Messages
15
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 :)
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
952
Office Version
2016
Platform
Windows
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)
 

HNarli

New Member
Joined
Jun 26, 2018
Messages
15
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,769

ADVERTISEMENT

"... will this still work?"
What happened when you tried it?
 

HNarli

New Member
Joined
Jun 26, 2018
Messages
15
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
 

HNarli

New Member
Joined
Jun 26, 2018
Messages
15

ADVERTISEMENT

"... will this still work?"
What happened when you tried it?
I haven't tried it yet as it's based over two spreadsheets and I don't understand that, sorry.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
952
Office Version
2016
Platform
Windows
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)
 

HNarli

New Member
Joined
Jun 26, 2018
Messages
15
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!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
952
Office Version
2016
Platform
Windows
You're welcome & thanks for the feedback.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,175
Messages
5,509,601
Members
408,743
Latest member
leen1234

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top