- 47
- 1 109 414
Hasaan Fazal
Pakistan
Приєднався 29 вер 2011
Keep Learning Keep Making!
Excel VLOOKUP: 5 Advanced LOOKUP Tricks You MUST Know
Learn how to use VLOOKUP in Excel in this beginner to advanced level tutorial for free.
Microsoft Excel and VLOOKUP Function are synonymous. In this quick guide, you will learn 5 (five) advanced VLOOKUP tricks including VLOOKUP with approximate match, VLOOKUP with wildcards, 2D VLOOKUP, or two-dimensional lookup, 3D VLOOKUP or three-dimensional lookup and finally how a single VLOOKUP formula can return multiple values or results.
Starting with the VLOOKUP vs IF comparison we learn how even a simple VLOOKUP function in Excel can outperform a complex nested IF formula. A must learn trick for every Excel beginner.
Using Excel VLOOKUP with wildcard characters like asterisk *, question mark ? and others, we can greatly enhance the usability of VLOOKUP formula in Excel in daily use. And we will learn how to apply VLOOKUP on text strings.
2D and 3D VLOOKUP are the most recommended techniques every avid Excel VLOOKUP user should learn. With these two techniques, you not only learn how to use MATCH function in combination with VLOOKUP formula in Excel but also how to jump data ranges based on criteria.
Lastly, we round up our series by learning how to use single VLOOKUP formula to return multiple values or results i.e. getting multiple results in one go!
Time Stamps:
00:00 Intro
00:15 VLOOKUP on data divided in brackets like Tax brackets
00:49 Simplifying the data in Excel for VLOOKUP application
2:19 VLOOKUP Function vs IF Function
3:30 Nested IF statements or Nested IF formula
3:53 VLOOKUP with Approximate match
4:43 How VLOOKUP Approximate match works
6:22 VLOOKUP with Exact match using wildcards
7:44 Wildcards - What and why they are needed
8:40 2D Vlookup or two dimensional VLOOKUP
9:35 VLOOKUP and MATCH Function
11:13 3D VLOOKUP or three dimensional VLOOKUP
12:07 BONUS Tip: How to name a data range quickly
13:02 VLOOKUP and INDIRECT Function
15:10 VLOOUP Array Formula
16:03 VLOOKUP Formula to return multiple values
Download the Fully worked Excel Workbook with sample data to practice along bit.ly/2P7iUF2
#MSExcel #VLOOKUP #LOOKTIPSnTRICKS
Microsoft Excel and VLOOKUP Function are synonymous. In this quick guide, you will learn 5 (five) advanced VLOOKUP tricks including VLOOKUP with approximate match, VLOOKUP with wildcards, 2D VLOOKUP, or two-dimensional lookup, 3D VLOOKUP or three-dimensional lookup and finally how a single VLOOKUP formula can return multiple values or results.
Starting with the VLOOKUP vs IF comparison we learn how even a simple VLOOKUP function in Excel can outperform a complex nested IF formula. A must learn trick for every Excel beginner.
Using Excel VLOOKUP with wildcard characters like asterisk *, question mark ? and others, we can greatly enhance the usability of VLOOKUP formula in Excel in daily use. And we will learn how to apply VLOOKUP on text strings.
2D and 3D VLOOKUP are the most recommended techniques every avid Excel VLOOKUP user should learn. With these two techniques, you not only learn how to use MATCH function in combination with VLOOKUP formula in Excel but also how to jump data ranges based on criteria.
Lastly, we round up our series by learning how to use single VLOOKUP formula to return multiple values or results i.e. getting multiple results in one go!
Time Stamps:
00:00 Intro
00:15 VLOOKUP on data divided in brackets like Tax brackets
00:49 Simplifying the data in Excel for VLOOKUP application
2:19 VLOOKUP Function vs IF Function
3:30 Nested IF statements or Nested IF formula
3:53 VLOOKUP with Approximate match
4:43 How VLOOKUP Approximate match works
6:22 VLOOKUP with Exact match using wildcards
7:44 Wildcards - What and why they are needed
8:40 2D Vlookup or two dimensional VLOOKUP
9:35 VLOOKUP and MATCH Function
11:13 3D VLOOKUP or three dimensional VLOOKUP
12:07 BONUS Tip: How to name a data range quickly
13:02 VLOOKUP and INDIRECT Function
15:10 VLOOUP Array Formula
16:03 VLOOKUP Formula to return multiple values
Download the Fully worked Excel Workbook with sample data to practice along bit.ly/2P7iUF2
#MSExcel #VLOOKUP #LOOKTIPSnTRICKS
Переглядів: 18 449
Відео
How to Make Multi Category or Subcategory Chart in Excel
Переглядів 41 тис.4 роки тому
Multi category charts or multi-level category charts are chart types with primary axis divided into multiple categories or subcategories. Such chart types help in visualizing data by dividing each item into different categories within an Excel chart. In this Excel tutorial, we will learn how to create a multi-category chart in Excel in easy to follow step by step approach. In addition to making...
Highlight Max and Min Values in Excel Charts with Different Colors - Conditional Format Excel Chart
Переглядів 7 тис.4 роки тому
In this Excel tutorial learn how to make better Excel charts by highlighting the maximum or minimum values in data range with different colors to make effective Excel graphs. This is one of the easiest ways to apply conditional formatting on Excel charts so that highest or lowest values within the data range are highlighted by plotting the figures in different colors. For any data like sales re...
Excel Custom Data Labels with Symbols that change Colors DYNAMICALLY with Data! - How To
Переглядів 20 тис.4 роки тому
In this tutorial we will learn how to format Data labels in Excel Charts to make them dynamically change their colors. And also how to insert any symbols in the Excel chart data labels. Make Data labels in Excel Charts dynamic to update with the data and change color make Excel charts amazingly effective and efficient as the users can know the results with a single glance without the need to "r...
Aging Analysis Reports using Excel Pivot Tables - How To
Переглядів 54 тис.4 роки тому
In this free Excel tutorial we are learning how to do conduct aging analysis using Excel Pivot Tables in few very simple steps. Using Excel Pivot Tables allows us to make flexible reports that can be customized in few clicks and help you make important accounting and managerial decisions quickly. In this tutorial, we will start with simple customer/supplier data with invoices to do aging or age...
How to Custom Sort in Excel using Custom Autofill Lists Super Fast!
Переглядів 3,8 тис.4 роки тому
In this Excel tutorial we are learning how to custom Sort Data by making and using Custom Autofill Lists. Custom Sort not only help us in sorting data beyond usual ascending and descending approach but also make sorting the data range flexible and more practical in real life. Time stamps: 0:11 Introduction of builtin Excel sorting options: ascending/descending 0:20 Sorting the Excel data in asc...
Create Custom Autofill Lists a.k.a AutoFill Series in Excel Super Easily
Переглядів 18 тис.4 роки тому
Ever wondered how Excel can easily and quickly give you list of months and days of the week? You never have to write all of them, even a single name is enough and Excel give you the rest once you drag the fill handle. It is all because of builtin Excel Auto fill lists. And if you don't you know you can make your own Custom Autofill lists to insert data quickly with complete control and convenie...
Auto Refresh Excel Pivot Tables + Power Query Connections If Source Data Changes
Переглядів 58 тис.4 роки тому
In this Free Excel Tutorial we are learning a simple line of VBA code that will automatically refresh Pivot Table report and Power Query Connections every time source data changes in Excel worksheet. Its quite a task if you have refresh pivot tables manually every time after making changes to source data range. But you can automate the whole refresh process by knowing just one line of code in V...
Make Dynamic Drop Down Lists that Auto Sort & Update from Unsorted data with Duplicates - 3 Methods
Переглядів 55 тис.4 роки тому
To make a workable and practical drop down list in Excel we need to get a sorted unique data for from the data range that is unsorted and has duplicates. And No VBA or Plugin is required for these Excel tricks. In this tutorial we learning three methods to make dynamic drop down lists in Excel by extracting unique list of items from the data range that is not alphabetical order and has duplicat...
Transpose Data in Excel | 5 Methods | Shift Data from Rows to columns or Vice versa
Переглядів 29 тис.4 роки тому
In this tutorial we are learning FIVE [5] different ways to transpose data in Excel. Transpose help change the orientation of data from rows to columns or columns to rows easily and quickly. With these 5 Excel tricks to transpose data in Excel you have freedom to choose whatever suits you and your data. 0. :: [0:12] Understanding Basics - What is TRANSPOSE, How it works and Why we need to know?...
Remove Formula but keep values/data in Excel | Excel Tricks | How To
Переглядів 89 тис.4 роки тому
In this quick tip Excel tutorial we are learning how to remove or delete the formulas in workbook but keep the values or data. This approach does not require any formula or coding and can be done in seconds if you know how to do it in Excel. In this video we learnt three different methods to use Paste Special to replace or remove formulas but keep the values or results in cells in swiftest poss...
Show 0 as Zero, Nil, Dashes or Hide Completely | Excel Tricks
Переглядів 23 тис.4 роки тому
In this Excel Quick Tip Tutorial we are learning how to display or show number 0 as a word Zero, Zilch, Nil, Nill, or dashes like - or and even how to hide the zeroes altogether from Excel workbook. More tutorials on Excel Custom Number Formatting: pakaccountants.com/category/custom-number-formatting/ FREE Excel Tutorials and Resources: pakaccountants.com/excel/ Fb: learnexceltoexc...
Freeze Any number of Rows or Columns or both in Excel
Переглядів 2,8 тис.4 роки тому
In this tutorial we will learn how to freeze a row or a column or both of them together in Excel in few simple steps. Its quite easy to freeze any number of rows or columns if you know the correct Excel steps of this trick. FREE Excel Tutorials and Resources: pakaccountants.com/excel/ Fb: learnexceltoexcel/ Tw: exceltoexcel/ Pin: www.pinterest.com/pakaccountants/learn-e...
Making Gantt Charts in Excel - How To | Free Tutorial
Переглядів 20 тис.5 років тому
In this episode of #MSExcel #FreeTutorial we are learning how to create Gantt Charts in Excel in few simple steps. #GanttCharts are used by analysts and managers to monitor the project by dividing the whole activity in meaningful tasks and their completion status or stage of completion. Excel charts can help create gantt charts easily and can update dynamically if more tasks are added to existi...
Excel Dynamic Named Data Ranges - Explained with examples feat. Data validation drop-down menus
Переглядів 5 тис.5 років тому
In this episode of #TutorialTuesday we will be learning about #Excel dynamic named data ranges in detail. Starting with what is dynamic data range, its benefits over static or normal data range and finally using the technique in Excel to create dynamic data validation drop down menus. This tutorial is bound to up your Excel game to new level! Fb: learnexceltoexcel Tw: e...
Actual vs Target Variance Charts in Excel with floating bars
Переглядів 7 тис.5 років тому
Actual vs Target Variance Charts in Excel with floating bars
Excel Shortcuts | CTRL+SHIFT Combinations to remember easily
Переглядів 18 тис.6 років тому
Excel Shortcuts | CTRL SHIFT Combinations to remember easily
20+ reason to love Excel Tables | Excel Table Tricks you MUST know!
Переглядів 24 тис.6 років тому
20 reason to love Excel Tables | Excel Table Tricks you MUST know!
Convert Numbers to Words in Excel - Free NUMWORD Add-in [Download/installation link in description]
Переглядів 18 тис.6 років тому
Convert Numbers to Words in Excel - Free NUMWORD Add-in [Download/installation link in description]
Excel Accounting Solution - Template
Переглядів 56 тис.6 років тому
Excel Accounting Solution - Template
Cash flow Reports in Excel Pivot tables from data on multiple worksheets
Переглядів 31 тис.6 років тому
Cash flow Reports in Excel Pivot tables from data on multiple worksheets
Making Profit & Loss Statements in Excel using Pivot tables
Переглядів 73 тис.6 років тому
Making Profit & Loss Statements in Excel using Pivot tables
Disable/Completely hide Windows 10 Task bar with no thin black line
Переглядів 92 тис.6 років тому
Disable/Completely hide Windows 10 Task bar with no thin black line
Aging Analysis Reports using Excel - How To
Переглядів 187 тис.6 років тому
Aging Analysis Reports using Excel - How To
Payoff tables + Risk Preference + Maximax, Maximin & Minimax Regret [Eng]
Переглядів 70 тис.6 років тому
Payoff tables Risk Preference Maximax, Maximin & Minimax Regret [Eng]
Incentive schemes - Labour Costing [Eng] - Part 4 of 4
Переглядів 5576 років тому
Incentive schemes - Labour Costing [Eng] - Part 4 of 4
Piecework Wage System - Labour Costing [Eng]
Переглядів 8 тис.6 років тому
Piecework Wage System - Labour Costing [Eng]
Incentive schemes - Labour Costing [Eng] - Part 2 of 4
Переглядів 5786 років тому
Incentive schemes - Labour Costing [Eng] - Part 2 of 4
Incentive schemes - Labour Costing [Eng] - Part 3 of 4
Переглядів 3736 років тому
Incentive schemes - Labour Costing [Eng] - Part 3 of 4
U r very bogus...not at all working
based video form 6 years ago that works KING
Very clear
You saved me from the madness. This is helping my soul. Thank you for recommending us this great software. The video explanation is on point. 👌
Glad it helped
Love this video ❤
Didn't work tasbar preview still shows.
NO INDI
Great share ❤
Thank you very useful
Just having a hard time getting multiple sheets to do this to one pivot table that is appended using power query
Excellent presentation as always. Thank you for using jargon followed by plain English. Double’s the learning experience.
Can u pls text the vlookup formula which u used The video is having very less resolution
Hi
Didnt work for me
What version of excel are you required to have?
please update this
Thank you so much this was so helpful!
Thank yiz Hasaan, amazing tricks. they enable to save a lot of times.
Hassan , thanks for the good work, I like the way you combine the excel formulas to solve a business problem. Also your voice is clear and the pace is good. I am now one of your UA-cam subscriber.
Very clear.. thanks a lot❤️
Please zoom the videos
Thank You good learning, its more excel technical, when compared to easy Pivot Table.
I love learning things like these! Thanks!
Vooral het laatste voorbeeld, especially the last example 👍
. Great Job!Thank you so much
not free
Thanks for the explanation!
Thanks! I really appreciate it!
after hiding the taskbar with the hotkey it does hide but once i click on the screen and hove the mouse over it appears again
Please help...I'm using data labels..in label options I'm selecting values from cells..in my data i have zeros and 0.2.. 0.9.. 0.6... 0.3...i want to hide zeros only .. how to do it?
how i received this add-in brother
Thank you so much
Awesome!
I have found a solution in cima -Kaplan for p1 where regret is calculated along alternative options, not along scenarios - I'm confused
staight to the point, thank you.
What happens with Start Menu when task bar is disabled? it still pops up when hitting windows key?
yes it do
Really useful and pretty clear explanation! Tks a lot!
Does it come with manual?
thank you ,, very useful just would like to know about the paid invoices.?? one column where i can add the invoice paid than it can be auto less.
Thank a lot, I have to buy Ms excel accounting software, please how is it possible.
Good one. Straight to the point.
Thank you! This works as intended! I switched my Windows theme to macOS... Much Appreciated :)
Thank you
Good one man, learnt alot
simple explanation. thanks a lot
Thanks for the great video! Any way to make this formatting apply to Labels that are selected from other Cell Values???
How to use this template? I have purchased but there is no manual