Excel Magic Trick 1489: 2 VLOOKUP in 1 Formula: Lookup Correct Volume Discount Product Price

preview_player
Показать описание

In this video learn how to create a single Excel formula that will lookup a product price and lookup the volume / quantity discount and deliver the correct discounted Product Price. See how to use the VLOOKUP Function with an Exact Match lookup and an Approximate Match Lookup. Also see how to use the ROUND Function to round to the penny.
  1. excelisfun
  2. Excel
  3. Excel 2016
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:08:56

Excel Magic Trick 1489: 2 VLOOKUP in 1 Formula: Lookup Correct Volume Discount Product Price

Excel Magic Trick 0:10:10

Excel Magic Trick 1499: VLOOKUP or INDEX Joins to Create Transaction Description (3 Examples)

Excel Magic Trick 0:04:07

Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date

Excel Magic Trick 0:05:43

Excel Magic Trick 1503: Free Book Give Away EMT 1493 – 1496 RAND & VLOOKUP Functions

EMT 1493 Part 0:15:37

EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Table

Excel Magic Trick 0:37:16

Excel Magic Trick 1471: Array Formula: Add Daily Rainfall 5 AM to 5 AM Next Day: (Most Automatic)

Excel Magic Trick 0:20:12

Excel Magic Trick 1482: SUMPRODUCT, DCOUNTA or SUM & IF for Counting with OR Logical Test

Excel Magic Trick 0:10:59

Excel Magic Trick 1475: Power Query, Formula, Flash Fill: '1-PSD/AFX-12' into '12-AFX...

Excel Magic Trick 0:08:43

Excel Magic Trick 1508: Lookup In Current Table? Self Join? VLOOKUP or Power Query?

Excel Magic Trick 0:08:42

Excel Magic Trick 1476: SUMIFS to Add Expenses for Any Time Period (Adding with Three Conditions)

Series Discount Excel 0:08:55

Series Discount Excel Formula. 4 Examples. XLOOKUP or INDEX? Excel Magic Trick 1643

Excel Magic Trick 0:05:13

Excel Magic Trick 1029: DON'T Use IFERROR, Use IF and Logical Test If Possible...

Excel Magic Trick 0:01:12

Excel Magic Trick 1505: Fill Data Automatically in Excel with Magic Fill Handle

Excel Magic Trick 0:07:46

Excel Magic Trick 1551: Lookup Discounted Price? 2 VLOOKUPs & ROUND

Excel Magic Trick 0:08:09

Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date

Excel Magic Trick 0:13:28

Excel Magic Trick 1511: Array Formula: Get Low Bid From Supplier When Delivery Less Than 20 Weeks

Excel Magic Trick 0:04:03

Excel Magic Trick 1491: “Lookup Latest Product Price” Survey Results, Winner of Free Book

Excel Magic Trick 0:10:54

Excel Magic Trick 1479: Lookup Bulk Quantity Discount Prices Using VLOOKUP Function

Excel Magic Trick 0:13:35

Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date

EMT 1497: Vote 0:01:49

EMT 1497: Vote For Your Favorite “Allocate Invoice Header Amounts To Transaction Line Table”

EMT 1495: Power 0:20:38

EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table

LOOKUP Beats XLOOKUP 0:02:49

LOOKUP Beats XLOOKUP at Approximate Match for Discounts, Taxes, Commissions. EMT 1828

Excel Shortcuts - 0:00:33

Excel Shortcuts - Rename Worksheet

DAX Approximate Match 0:20:02

DAX Approximate Match Lookup & Exact Match Lookup Together for Discounted Product Price (EMT 149...

Комментарии
Автор

Brilliant example of VLOOKUP! Thank you!

MalinaC
Автор

Thank you for the double VLOOKUP fun today!

LeilaGharani
Автор

2 vlookups at once...a nice double play! Thanks again for the videos Mike.

DougHExcel
Автор

Very helpfull when you don't have space for helper columns. Thank you.

JanBolhuis
Автор

Dear Mr. Mike I'm really so sorry i was busy these two weeks that's why i didn't see the videos. thank you so much the trick is great

ismailismaili
Автор

Mike,

To select my data in a table I press CTRL+SHIFT+LEFT ARROW+DOWN ARROW, and then to go back to my formula I hit CTRL+BACKSPACE. What if I am doing the selection on a different sheet, what would I use in place of CTRL+BACKSPACE?

Well, to get around it, I just made the data on the second worksheet into a table and gave it a name and and selected the name in my formula.

but, if you do know of a keyboard shortcut, pls let me know.


Thanks again.

malluk
Автор

Did the formula take the correct discount though? I see the same pricing on both the unit price and the discounted formula column.

JAT
Автор

=vLookup(The excel flavorer, Mike), control+enter, the returned answer is "like"
Thank you man
I'm just trying to express myself in an excel way

arahmanabdelrahman
Автор

thank you sir as usual you'r the best in excel thank's again

Hackzinou
Автор

Thanks for this EXCELlent video amazing Mike.

SyedMuzammilMahasanShahi
Автор

Nice friendship of approximate and exact vlookups

vida
Автор

Piece of cacke this EMT mike :-) Thanks for the perseverance on making videos for us

mohamedchakroun
Автор

Great video, Mike, as per usual, albeit not the most complex one you’ve ever made.
Now, can you take it up a notch and do the same in Power Pivot Data Model?
(Or are you simply going to refer me here to EMT 1137? ;-)

GeertDelmulle
Автор

Loving your feed. Hi from San Francisco.

ChirasreeBanerjee