200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
I have two tables:
1) code | name | price | city | quantity
1234 | name1 | 100 | north
2) code | city | quantity| price
1234 | north | 0-50 | 60
1234 | south | 0-50 |60
1234 | north | 51-100| 70
1234 | south | 51-100| 70
now how to use vlookup or another function to tell that take code, city and quantity in table 1 and search for them in table 2, once found them correspondencies show me the price_
I'm not certain that you're actually looking for a Double Vlookup, it seems to me like you are just trying to get a unique lookup value from the data tables that you currently have where there is no single unique value in a single column.
If that's the case, then create another column and concatenate all the other columns into that new one and use that new one to create and search for a unique value. If you use a Vlookup for the lookup, then make sure that the new column is to the left of the other columns. Also, you can hide the new column if you don't want other users to see it.
If you want an double vlookup, check out TeachExcel's youtube video for this and you will get a nice walkthrough of how to do it: Double Vlookup in Excel
I created the following named ranges, at workbook level, on the sheet which corresponds to your lookup table.
A2:A10 = Code
B2:B10 = City
C2:C10 = Minimum
D2:D10 = PriceThe advantages of using names instead of sheet addresses is that (a) "Code" is shorter than Sheet2!$A$2:$A$10, (b) "Code" is easier to understand when you read it in a formula and (c) changing the address of a named range can be done in one location whereas cell addresses have to be changed in every formula. With that said, you can replace the names in my formula below with cell addresses. Also observe that the ranges must be of identical size for the formula to work.
Your expression in the Quantity column can't be made to work easily. Therefore I have replaced the column contents with a single number and called it "Minimum". There is one price for "no minimum" (meaning "a quantity larger than 0" - cell is blank or 0) and another for a minimum of 50 (meaning "a quantity larger than 50 - cell shows 50). "Minimum" isn't the best word but that is how the formula works. The down-side of this system is that the formula can't return a price if both Minimum and Quantity are 1 (or no Minimum exists which is smaller than Quantity). Make sure you have a blank (or 0) Minimum for every item in your list.
=IFERROR(AGGREGATE(15,6,Price/((Code=$F$2)*(City=$G$2)*(Minimum<$H$2)),1),"N/A")This formula has the lookup values in row 2. Code = F2, City = G2 and Quantity in H3. The idea is that you should enter the quantity actually being sold and the formula will return the correct price. E.g. if the q'ty is 1-50 the higher price will be returned, if it is 51 or more that lower price will come up. You can have more than 2 brackets in your list. Please change the references (F2, G2 and H2) to match your needs.
The formula will return "N/A" if either Code or City wasn't found or there is no Minimum which is smaller than Quantity. You can change the return string in the formula to anything that suits your fancy.
Finally, a word about the function itself. It will return the lowest price in the list which meets all the criteria. This is because (Minimum<Q'ty) can return more than one value. So, the final selection is made based on price. If you want to sell larger quantities at a higher price the function will not work in its present configuration.