Double vlookup to search in vertical and horizontal

0

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_

Answer
Discuss

Answers

0

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

Discuss
0

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 = Price

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

Discuss

Discussion

I have a little trouble naming the columns and getting  the values. Can you show me more specifically? can i include the spreadsheet here?
DanielK Jan 30, '19 at 2:52 am
When you click on a cell you can see the cell contents (value or formula) in the Formula Bar above the columns ruler. You can also edit the contents there. To the left of the Formula Bar you see the Name Bar.  Most of the time it shows the cell address. Now, select the range A2:A10. The Name Bar shows "A2". Replace the "A2" with "Code" and you have created a named range by the name of "Code". Now when you select A2 the Name Bar will show "A2" as before but when you select A2:A10 it will show the range's name, "Code". To modify a named range use the Name Manager on the ribbon's Formulas tab. Click the Edit button and modify the address in the Refers To field at the bottom of the dialog box. If you change the range's name all formulas using that name will be updated automatically.
Variatus (rep: 2713) Jan 30, '19 at 3:46 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login