Excel Magic Trick 963: Lookup Product Price Based On Quantity: Two Way Lookup
HTML-код
- Опубликовано: 22 авг 2024
- Download Excel Start File: people.highlin...
Download Excel File: people.highline...
Lookup Product Price Based On Quantity: Two Way Lookup:
1. INDEX function for two way lookup and then MATCH function with Exact Match for Row number and MATCH function with approximate match for quantity
2. VLOOKUP function to do an exact match for product name and then MATCH function with approximate match for quantity to determine the column umber in the third argument of VLOOKUP
This video covers the classic two-way lookup problem and will compare and contrast two methods:
1) INDEX and 2 MATCH functions
2) VLOOKUP and MATCH functions
Awesome! I am glad that this video finally hits home how to use VLOOKUP and MATCH to do two-way lookup! Thanks for Liking the video!
I am glad that you liked it!
Thank you Mike!! After all the examples I've seen of this I never seemed to grasp it and be able to apply it to my work. These made it clear and I rely on Vlookup so much and I love the way you include the match so I don't always have to put in the column number to return. Now I have a way to look that up too.
Fantastic video, so much and so clearly explained in such a short time. In the beginning struggled to get my head around VLOOKUP and found the best explanations in your other videos. But this is what I have been trying to work out how to do since I started my Excel Odyssey this year: how to build a 2 way look up to pull prices from a table for different fabrics that are priced depending on the meterage used. Finally my calculation sheet is coming together. Thank you! 😂
This was just what I was looking for! Slightly different problem but I was able to solve for it using your instructional video. Thanks!
It worked because the product names are sorted. If the Product names are ALWAYS sorted then it is better to use the approximate because it makes a much faster calculating formula. Further, if your list is ALWAYS sorted, then don't put TRUE, leave the entire 4th argument out because VLOOKUP knows to use approximate when it is left out - that is the default. If your lookup column is not sorted and you want to find an exact match, you must use 0 or FALSE.
Thank you for the trick! Your instruction is the easiest, most logical way to understand excel functions and how it can combine in comparison with other instructions I read and watched in the internet.
Now to me Excel is really cool! I will subscribe you.
One note from my experience: if after updating formula for whole rows, your results is the same for the whole rows, you just need to save the document and the results will be truly updated.
Yes it is wonderful. nice effort with great excel file.
but i have scenario that the Price table is vertical as like below. then it doesnt works.
Item Quantity Price
Pro 1 1 22
Pro 1 20 20
Pro 1 35 18
Pro 2 1 37
Pro 2 20 34
Pro 2 35 31
Pro 3 1 42
Pro 3 20 38
Pro 3 35 35
You are welcome!
Awesome job, amazing tricks. Thanks you so very much for sharing all these vids. Can't thank you enough!!
that is PURE MAGIC! you are so cool! i've learned a lot from your vids! thanks!
It is last one in Excel Magic Trick group.
Thanks Mike! I finally get index and match like I never did before. What if, however, your discount was based on the total amount of the items listed; lets say in 3 different total ranges?
Try refreshing the browser. Or try a different browser. I tried IE and CHROME and both seemed to work.
Great explanation! Thanks!
Cool!
Thank you for the easy to understand tutorial.
What if some of the products have different range for pricing.
Example: Product 1 has $5 for qty within 1-5
but Product 2 has $6 for qty within 1-8 ?
Thanks Mike, you da man.
Thanks, krn14242!
--Guy Having Fun With Excel
In the vlookup example I changed the False to a True and it still worked in each case. Is there a time when it won't?
For index match match I am switching from tab to tab... how do I do that?
I understand, but file named "Workbook EMT 960-962.xlsx" not link, so i can't download. it's same to the files "Workbook EMT from 950 till 959".
How can i change the date format from 07/14/2019 to 14/07/2019? please help me
Guys, can someone help me? Can't download workbooks on people.highline.edu ther are no files matched to this video. Thank you.
WorkbookExcelMagicTricks949 it's last active link.
Thank you, chrome works. (opera s*cks)
I am glad that you liked it!
Try refreshing the browser. Or try a different browser. I tried IE and CHROME and both seemed to work.