Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

Lookup Functions in Excel: Learn All 6 Functions

Admin
Duration: 1:03
Submitted: 3 months ago
Views: 245

Comments (0)

Link to this video:

Description: In this video, we are going to learn about lookup functions in Excel. Lookup functions are used for various purposes such as analysing your worksheet or sorting data. We will be learning six lookup functions in this video: Vlookup, HLookup, Xlookup, Match, Index and Choose. We will also look at some basic practical examples of how these functions can be used. Stay tuned! Table Of Content 00:00 Introduction 01:21 Vlookup Function 05:56 HLookup Function 07:59 XLookup Function 10:50 MATCH Function 12:43 INDEX Function 15:37 CHOOSE Function 17:30 Conclusion In the first part, we will learn about the VLOOKUP function in excel. VLOOKUP stands for vertical lookup. It is a function used to find the value in the first column of a table, which matches a specific criteria you have mentioned. This can be very useful when you have data stored in different tables and want to extract relevant information from them by matching values from one table against another. Suppose we have the following data: In the first column, we have employee names and in the second column, we have their salaries. We want to find out the salary of a specific employee, say John. To do this, we will use VLOOKUP function. The formula for VLOOKUP is as follows: =VLOOKUP(value to lookup, table range, column number) In this formula: - Value to lookup is the value we are looking for. In our case, it is John's name which we will enter as "John". - Table range is the table in which we want to search for the value. In our case, it is B11:C14 in which we have the employee names and salaries. - Column number here, refers to the column from where we want to retrieve our data. Since John's Salary is stored in the second column of this table, we will enter "B" as input for this argument. So, let's try entering "John" as the value to lookup, B11:C14 as table range and "B" as column number. We will now see that we have successfully retrieved John's salary from this table using VLOOKUP function. If you want to learn it in detail then please watch my video on Vlookup function where I have covered it with good examples. Similarly, you can use VLOOKUP for finding data from any column in a table by entering the relevant column number. We will now look at another lookup function called HLookup. This is similar to Vlookup except that it is used to find values horizontally instead of vertically as was done in case of Vlookup. HLOOKUP stands for horizontal lookup and is used to find a value in the first row of a table, which matches the specific criteria you have mentioned. Suppose we have the following data: In this table, we want to find out what are the sales values for different products. To do this, we will use HLookup function. The formula for Hlookup is as follows: =HLOOKUP(value to lookup, table range, row number) In this formula: - Value to lookup refers to the value we are looking for. In our case it would be "Product A" which we will enter as "A". - Table range is the table in which we want to search for the value. In our case it would be B11:D14 where we have data of different products and their corresponding sales values. - Row number refers to the row from where we want to extract our data. Since we want to know the sales values for each product, we will enter "11" which is the row number from where their sales values are stored. So, let's try entering "A", B11:D14 and 11 as inputs in this formula. We will now see that it has successfully extracted the value of Sales A from our table using HLookup function. If you want to learn it in detail then please watch my video on Hlookup function where I have covered it with good examples. Similarly, we can use this function for finding data from any row in a table by entering the relevant row number. We will now look at another lookup function called XLookup which is similar to Vlookup and HLookup. The difference is that this function allows us to lookup values both vertically and horizontally in a table by using the same formula. XLOOKUP stands for extended lookup which is an improved version of VLOOKUP or Hlookup functions because it can look up values both vertically as well as horizontally, unlike the previous two functions which can only look up values either vertically or horizontally. Suppose we have the following data: This table has sales data of different products for various months. We want to find out what are the sales of product A in March. To do this, we will use XLookup function. The formula for XLookup is as follows: =XLOOKUP(value to lookup, table range, column number) In this formula: - Value to lookup refers to the value we are looking for. In our case it would be "Product A" which we will enter as "A". - Table range is the table in which we want to search for the value. In our case it would be B11:D14 where we have data of different products and their corresponding sales values. - Column number refers to the column from which we want to retrieve our data. Since March's sales is stored in the third column of this table, we will enter "C" as the column number. So, let's try entering "A", B11:D14 and "C" as inputs in this formula. We will now see that it has successfully extracted the value of Sales A for March from our table using XLookup function. If you want to learn it in detail then please watch my video on XLookup function where I have covered it with good examples. We can also use this function to lookup values from any column in a table by entering the relevant column number. Next, we will look at another lookup function called Match which is used to find the position of a given value in a list or array. MATCH stands for match which is a function used to find the position of a given value in a list or array. Suppose we have the following data: In this table, we want to know what is the position of product A in the list. To do this, we will use Match function. The formula for Match is as follows: =MATCH(value to lookup, table range) In this formula: - Value to lookup refers to the value we are looking for. In our case it would be "Product A" which we will enter as "A". - Table range is the list in which we want to search for the value. In our case it would be B11:B14 where we have data of different products. So, let's try entering "A" and B11:B14 as inputs in this formula. We will now see that it has successfully extracted the position of Sales A from our list using Match function. If you want to learn it in detail then please watch my video on Match function where I have covered it with good examples. Similarly, we can use this function for finding the position of a given value in any list or array. We will now look at another lookup function called Index which is used to extract a value from an array or range based on the position of that value. INDEX is a function used to extract a value from an array or range based on the position of that value. Suppose we have the following data: In this table, we want to know what are the sales figures for product A in March. To do this, we will use Index function. The formula for Index is as follows: =INDEX(table range, row number, column number) In this formula: - Table range is the table in which we want to search for the value. In our case it would be B11:D14 where we have data of different products and their corresponding sales values. - Row number refers to the row from which we want to extract our data. Since March's sales is stored in the third row of this table, we will enter "C" as the row number. - Column number refers to the column from which we want to retrieve our data. Since March's sales is stored in the second column of this table, we will enter "B" as the column number. So, let's try entering B11:D14 and C and D as inputs in this formula. We will now see that it has successfully extracted the value of Sales A for March from our table using Index function. If you want to learn it in detail then please watch my video on Index function where I have covered it with good examples. Similarly, we can use this function for extracting the value of any cell from an array or range based on its position. Lastly, we will look at another lookup function called Choose which is used to return a value from a list of values based on the position of that value. CHOOSE is a function used to return a value from a list of values based on the position of that value. Suppose we have the following data: In this table, we want to know what are the sales figures for product A in every month. To do this, we will use Choose function. The formula for Choose is as follows: =CHOOSE(index number, value, .....) In this formula: - Index number refers to the position of a given value in a list of values. In our case it would be "Product A" which we will enter as "A". - Value refers to the list of values in which we want to search for the value. In our case it would be B11:D14 where we have data of different products and their corresponding sales values. So, let's try entering "A" and B11:D14 as inputs in this formula. We will now see that it has successfully returned the sales value for product A in every month from our table using Choose function. If you want to learn it in detail then please watch my video on Choose function where I have covered it with good examples. These are the six lookup functions which we can use in Excel for various purposes. I hope this article was helpful. If you have any queries then please write it down in the comments section below and I will reply to you soon. Thanks for watching this video. Goodbye!