# Vlookup cross reference two parameters

#### HNarli

##### New Member
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.

 A B C Supplier Quantity Break Point Discount Ford 1 0% Ford 5 10% Ford 10 20% Ford 20 30% Ford 50 40% Vauxhal 1 0% Vauxhal 5 5% Vauxhal 10 7%

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

 X Y Z AA Supplier Qty Supplier Discount Price Ford 10 Using 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 quantity I’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

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

##### Well-known Member
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)

#### mikerickson

##### MrExcel MVP

=MAXIFS(C1:C100, A1:A100, "Ford", B1:B100, "<=10")

#### HNarli

##### New Member
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

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

#### HNarli

##### New Member
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

"... 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.

##### Well-known Member
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
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!

##### Well-known Member
You're welcome & thanks for the feedback.

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...