You are reading the article Vlookup In Excel (Formula, Examples) updated in February 2024 on the website Katfastfood.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Vlookup In Excel (Formula, Examples)
VLOOKUP in Excel (Table of contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersIntroduction to VLOOKUP Function in Excel
VLOOKUP in Excel is used to lookup the value with a reference cell and fetch the value from the selected lookup table array and is quite useful and one of the most widely used excel functions We can use a table or single column to lookup the value. And all the lookup can be done in a vertical zone or with columns only.
The vlookup function uses of the arguments:-
There are four arguments in vlookup function which is below mention:
Lookup value (required argument) – it is the value that we want to look up for in the column of a table. Where you want or get the value from another table.
Table array (required argument) – it is the data array that is to be searched. The vlookup function searches in the left-most column of this array. We can say that this is a matching table.
Column index number (required argument) – an integer, specifying the column number of the supplied table array, that you want to return a value from. If you only using it for data matching then you can put 1 but if you want to get a value from another column behave on matching the lookup value then you need to put the column no from matching column no.
Range lookup (optional argument) – it defines what this function should return in the event that it does not find an exact match to the lookup value. The argument can be set to true or false, which means:
True – approximate match, that is, if an exact match is not found, use the closest match below the lookup value.
False – exact match, that is, if an exact match not found then it will return an error.
We can also use 0 for false and 1 for true matching.Steps for Using VLOOKUP Function
We get a new function window showing in the below mention pictures.
Then we have to enter the details as shown in the picture.
Put the lookup value where you want to match from one table to another table value.
You need to put the table array which is another table value.
Put the col index number for another table vertical value which is a need.
Rage lookup false for exact match and true for an approximate match.
You can also use 0 for an exact match and 1 for an approximate match.
Shortcut for using the Formula
Explanation for VLOOKUP Function:
We can also use one sheet to another sheet and one workbook to another workbook alsoHow to Use VLOOKUP in Excel?
Vlookup function is very simple and easy to use. Let us understand the working of vlookup. Below mention are the details using the formulas.
You can download this VLOOKUP Function Template here – VLOOKUP Function TemplateExample #1 – Exact Match
To search for an exact match, you put false in the last argument.
As above mention in table b there is all information of the employee like department, employee id, address mobile no, etc. You can suppose as array table or master table data. And you have another table where required only contact no of the employee. So as the employee name is a unique column then the employee name a lookup value in a table where you want to get a result.
Master data table b as above showing is a table array, and in master data, you can see that mobile no column is on the 5 number column index. Then we need to put the 0 for exact matching or 1 for false matching.
You can see the result here:
Formula: – “=vlookup(a21,a1:e12,5,0)”
We can see that in a table all values are matching the exact value.Example #2 – Approximate Match
As above showing in the picture in the H column, there is employee age mention and column I employee name. You can take this as an array or master table. Now I want to put the age value in k2 then we get the employee name which is approximate age as given in k2 age value as showing below mention pictures.
So lets we check the formula now.
Then we can get the approximate matching value.
As you see, the formula returns Mr. Puneet Sharma whose age is 43, while we also have Mr. Manish patial that age 48 but 43 is much closer to 44 than 48. So, why does it return Mr. Puneet? Because vlookup with approximate match retrieves the closest value that is less than the lookup value.Things to Remember
The vlookup function returns result in any data type such as a string, numeric, date, etc.
If you specify false for the approximate match parameter and no exact match is found, then the vlookup function will return #n/a.
If you specify true for the approximate match parameter and no exact match is found, then the next smaller value is returned.
If the index number is greater than the number of columns in the table, the vlookup function will return #ref!Recommended Articles
This has been a guide to VLOOKUP Function. Here we discuss the VLOOKUP Formula and how to use the VLOOKUP function along with an excel example and downloadable excel templates. You may also look at these useful functions in excel-
You're reading Vlookup In Excel (Formula, Examples)
CORREL in Excel
The correl function in Excel is used for calculating the Correlation Coefficient, whose value ranges from -1 to +1 only, and it also shows how strongly any 2 values are related. The range for the correlation coefficient is only -1 to +1, which is quite small, and the value falling under this range will be less compared to any other number. As per the syntax, we just need to select the 2 arrays of numbers for which we need to find the Correlation Coefficient.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersCorrelation coefficient
It’s a statistical measure of how strong a relationship is between two variables, i.e., for a positively correlated variable, the Correlation coefficient value increases. In contrast, for a negatively correlated variable, the Correlation coefficient value decreases.
Correlation coefficients express values between +1 and -1.
I have two variables (one plotted on the X-axis, one on the Y-axis)
If the value is 1, then it means a strong positive correlation. In this case, y increases when x increases (Positive linear relationship)
If the value is 0: means that there is no relationship between the two variables (x and y)
If the value is -1, then it means a negative correlation: In this case, y decreases when x increases (Negative linear relationship)
DefinitionCORREL Formula in Excel
Below is the CORREL Formula:
Array1– It is an independent variable. It is entered as a cell reference or range of values.
Array2– It is a dependent variable. It is entered as a second cell reference or range of values.
Correlation coefficients are expressed as values between +1 and -1.
A coefficient of zero indicates, No discernable relationship between fluctuations of the variables.
It is most commonly used to Calculate the correlation coefficient for two sets of values or variables, i.e., the Correlation between a particular stock or share price and the market index value.
To Calculate the correlation coefficient for refrigerators & air conditioner sales in the summer & winter seasons and Car models, their year of launch & price difference.
Excel Correl function is similar to Pearson Function
CORREL function is used as a worksheet function & also in Excel VBA.
CORREL function in Excel is easy to use & is a very simple function with few argumentsHow to use CORREL Function in Excel?
CORREL Function is very simple to use. Let us now see how to use the CORREL function in Excel with the help of some examples.
You can download this CORREL Function Excel Template here – CORREL Function Excel TemplateExample #1
For a Set of Positive Variables or Dataset
With the help of the Correl function, I need to find the correlation coefficient between two datasets or variables.
In the below-mentioned example, the table contains two variables, one in column X & the other in column Y. where both the datasets contain positive values.
Let’s apply the Correl function in cell “C14”. Select the cell “C14,” where the Correl function needs to be applied.
A dialog box appears where arguments for the CORREL function need to be filled or entered, i.e., =CORREL(array1, array2)
i.e. =CORREL(B8:B12,C8:C12) will appear in cell C14
i.e. =CORREL(B8:B12,C8:C12) returns 0.988104929 as the result. The Correlation coefficient between the two datasets or variables is 0.98
To use a line chart for graphical representation, choose the chart option for “line chart”. It will allow you to represent data using a line chart visually.
I have two variables, X & Y, where one is plotted on the X-axis and the other one on the Y-axis.
Select the table range excluding header X & Y, i.e., B8 TO C12
It will result in a chart,
Chart elements such as legend series (X, Y) axis title (X & Y axis), chart title (POSITIVE CORRELATION) & data label (Values) need to be updated in the chart.
You can see a strong positive correlation, i.e., Variables X & Y values are positively correlated (Positive linear relationship)Example #2
For a Dataset Containing Positive & Negative Values
With the help of the Correl function, I need to find out the correlation coefficient between two datasets or variables
In the below-mentioned example, I have two variables, one in column x & the other in column Y. where column X datasets contain positive values & column Y datasets contain negative values
Let’s apply the Correl function in cell “C29”. Select the cell “C29,” where the Correl function needs to be applied.
A dialog box appears where arguments for the CORREL function need to be filled or entered, i.e., =CORREL(array1, array2)
i.e. =CORREL(B23:B27,C23:C27) will appear in the cell C29
i.e. =CORREL(B23:B27,C23:C27) returns -0.988104929 as the result. The Correlation coefficient between two datasets or variables is -0.98
Similar to the above example, it graphically represents using a line chart under chart options
I have two variables, X & Y, where one plot on the X-axis, the other one on the Y-axis
You can see the negative correlation, i.e., Variables X & Y values are negatively correlated (Negative linear relationship). In this case, y decreases when x increases.Things to Remember
Suppose Array1 and Array2 have a different number of data points or if the supplied arrays are of different lengths. CORREL results in or returns the #N/A error value.
The function will return the #DIV/0! Error value if the argument array1 or array2 contains non-numeric data (text, logical values, or blank cells).
It’s an inbuild Analysis Toolpak Add-in which is present in application add-ins.Recommended Articles
Although this has been a guide to the Excel CORREL function. Therefore, here we discuss the CORREL Formula and how to use the CORREL function in Excel, along with practical examples and a downloadable Excel template. Thus, you can also go through our other suggested articles –
NETWORKDAYS in Excel
Networkdays function simply returns the number of working days between two dates and also considers the holidays if there are any (Optional). By this, we get to know the number of working days as well, and also, it is quite helpful for counting the number of working days spent on any activity or project to have an idea track or can be used for any other purpose.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersUses of NETWORKDAYS Function in Excel
The NETWORKDAYS function calculates the working days between two dates in Excel. This function automatically excludes weekends (Saturday and Sunday) and holidays. This function also excludes the specified holidays from the working day calculation.
NETWORKDAYS Formula in Excel:
Below is the NETWORKDAYS Formula in Excel :
Where the supplied arguments are as follows:
Start_date – The start_date from where you want to start calculating work days.
End_date – The end_date up to which you want to calculate working days.
Holidays – This is an optional argument. We want to exclude the list of holidays (one or more dates) from the working day’s calculation. This list can be entered as a cell, a range of cells, or an array containing dates.
As a worksheet function, the NETWORKDAYS Function can be entered in Excel as per the below screenshot:
The NETWORKDAYS function is a built-in function in Excel; thus, it can be found under the FORMULAS tab. Please follow the below steps:
Select the Date & Time functions category.
It will open up a drop-down list of functions. Select the NETWORKDAYS Functions from the drop-down list.
It will open a dialog box of Function arguments.
Fill in the start_date field; it can be entered as serial data, which calculates the working days.
Fill the end_date field; it can be entered as the serial date and used in the calculation.
Enter the holiday field, which is an optional argument. It is the set of one or more dates that we want to exclude from the working day’s calculation.How to Use the NETWORKDAYS Function in Excel?
You can download this NETWORKDAYS Function Excel Template here – NETWORKDAYS Function Excel TemplateExample #1
Let’s take some dates as start date and end date.
Below dates are the holidays in between:
Now for calculating the number of working days, the start date, end date, and holidays date have been entered as serial dates.
The result will be :
Drag & drop this formula for the rest values, and the final result is as shown below:Example #2
It depends on the solution, that does we want to exclude only weekends or do we want to also exclude the holiday dates too from the working day’s calculation.
We have given the below dates, and we will do both types of calculations:
Apply NETWORKDAYS Function to the cell E26.
The Result will be :
The final result is shown below:Things to Remember
NETWORKDAYS Function automatically excludes both Saturday and Sunday (takes both as weekends). If you want to use different days, like weekends, you need to use a different function for this, i.e. chúng tôi function.
If you pass a range of cells for the holidays, make the range an absolute value. So that the range will not change if you drag this formula to other cells.
The function returns the #VALUE! error value if any date passed as an argument is invalid.
If the start date is later than the End Date, the NETWORKDAYS Function will return a Negative value.Recommended Articles
This has been a guide to NETWORKDAYS Function in Excel. Here we discuss the NETWORKDAYS Formula in Excel and How to Use NETWORKDAYS Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
LARGE Function in Excel
A large function in excel is an inbuilt statistical function that returns the nth position or Kth position from the selected numerical array. If the Kth position is greater or larger than the values, there is an array, or if we keep the Kth position blank, it will return #Num! As error. This means while putting the Kth value in the syntax, we need to put the value which is the lowest number from the selected array or any lowest number, but it should not be in an array.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
This function can sort the information provided and find the maximum value.LARGE Formula in Excel
Below is the LARGE Formula:
Array–the array or range of data from which we want to select the largest value.
K – An integer value specifies the position from the largest value.
As the LARGE function falls under the Statistical function category, and it is a built-in function in Excel, i.e. this function can be found under the FORMULAS tab in Excel. The steps are as follows:
Select the Statistical functions category. It will open the drop-down list of functions.
After selecting the LARGE function, a Function arguments box will pop up.
Enter the Array field with an array or range of data you want to find the nth largest value.
Enter the K field; it is the position from the largest value in the array of the value to return.How to use the LARGE Function in Excel?
LARGE Function is very simple to use. Let us now see how to use a LARGE function with the help of some examples.
You can download this LARGE Function Excel Template here – LARGE Function Excel TemplateExample #1
Suppose we have company employee data, and the company distributes incentives to their employees based on their performance. We need to find out the name of the employees who are in the top 3 on the list and received the highest incentives.
To find the employee name who has achieved the largest incentive, will use the below formula:
The Result is:
To find the employee name who has achieved the second-largest incentive, will use the below formula:
To find out the employee name who has achieved the 3rd largest incentive, will use the below formula:
The Result is:
The final results are:Example #2
Let’s consider the below example with some values.
We need to arrange the above data in the order of Largest to smallest. With the help of the LARGE function, we can do this very easily.
Similarly, we find other valuesExample #3
Let’s assume Sales data is given, and we want to see the total sales from the top 5 performers.
We want to see the sales done by the top 5 sales employee:
We will apply the LARGE function to select the top 5 sales performers by passing the positions from 1 to 5 as an array as a second argument (k) position and summing those values.
The formula used for solving this problem is:
The final result is:Things to Remember
If the LARGE function returns the error value #NUM!, it means
The second argument value (k) is less than 1 or greater than the number of values in the given array.
The given array is empty.
Suppose the function returns the error value #VALUE! – means the second argument (k) is non-numeric.
The LARGE function is used to sort the data.
This has been a guide to Excel LARGE Function. Here we discuss the LARGE Formula and how to use the LARGE function, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
The VLOOKUP function in Microsoft Excel literally means vertical lookup. It’s a search function for querying values in the cell of a column. This function searches for the data relative to the entries in the first column from the left.
A vertical data search is most vital when dealing with tables with numerous columns and rows. Instead of scrolling through and analyzing hundreds of cells, Excel’s VLOOKUP function helps you find the data you’re looking for by looking up the values from top to bottom.Create, build & use Excel’s VLOOKUP function
In our example, we’ll work with a VLOOKUP function that searches for information about seven employees’ salaries. This section shows you how to use the VLOOKUP function in the following ways:
Write the Excel VLOOKUP function.
Build a VLOOKUP function in Excel.
Without further ado, let’s get to it. In the first method, we’ll create the function manually. Next, we’ll use it from Excel’s inbuilt Functions Arguments wizard.1] Write the Excel VLOOKUP function
Launch Microsoft Excel and make a column for the values that act as unique identifiers. We’ll call this the reference column.
Add some more columns to the right-hand side of the first one you created in the first step and insert values for the cells in these columns.=VLOOKUP()
On entering the above formula, Excel suggests the VLOOKUP syntax:=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup) Arguments or parameters
Here are what the above arguments define in the syntax:
lookup_value: the cell with the product identifier from the reference column.
table_array: the data range from with to search. It must contain the reference column and the column containing the value you’re looking up. In most cases, you can use the entire worksheet. You can drag your mouse over the values of the table to select a data range.
col_index_num: the number of the column from which to look up a value. You put this in from left to right.
range_lookup: TRUE for an approximate match, FALSE for an exact match. The value is TRUE by default, but you generally use FALSE.
With this information, we’ll now replace the parameters in the parenthesis with the information we wish to look up. For example, to return Wayne Creed‘s salary, enter the following formula:=VLOOKUP(C14,B5:E11,6,FALSE)
On navigating away from the cell with the VLOOKUP formula, it returns the value for which you queried. If you get a #N/A error, read this Microsoft guide to learn how to correct it.2] Build a VLOOKUP function in Excel
The first part showed you how to create a VLOOKUP function manually. If you thought the above method was easy, wait till you read this. Here, you’ll learn how to build a VLOOKUP function quickly using the user-friendly Functions Arguments wizard.
Open Microsoft Excel first, and create a reference column that will contain unique identifiers.
Next, create some more columns on the right-hand side of the reference column. Here, we’ll insert the relevant values for the items on the reference column.
Select an empty cell and type in a value from the reference cell. This is the value whose properties we’ll lookup.
Select the Lookup & Reference tool from the Functions Library and choose VLOOKUP from the dropdown menu. This opens the Functions Arguments wizard.
Fill in the Lookup_value, Table_array, Col_index_num, and Range_lookup fields in the Functions Arguments wizard specified in the first method.
Hit the OK button when you’re done, and the VLOOKUP function will return the results from the arguments you entered.
This guide will help you if the Excel formula fails to update automatically.
Both methods will successfully query the data you need in reference to the first column. The Formulas Argument wizard makes it easy to input the variables to make the VLOOKUP function work.
However, the VLOOKUP function also works on the web version of Excel. You also get to use the Functions Argument wizard or create the VLOOKUP function manually on the web version.
Let us take a look at the HLOOKUP function in Excel now.
VLOOKUP with True
When we apply the Vlookup function, it is obvious that we always look for the exact match. When we get the #N/A, we also assume that the value we are looking for is unavailable in the lookup table. But we have yet to try using Vlookup with a TRUE value match. We can use two criteria in Vlookup functions, which are TRUE and FALSE. FALSE gives us the exact match, whereas TRUE gives us the approximate match for the value if not in the lookup range. Vlookup True is used when we don’t get an exact match, but with the help of TRUE, we can get an approximate match or near value to the value which we are looking for.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersHow to Use Vlookup True?
To understand the use of Vlookup True, first, we need to understand the syntax of Vlookup. Below we have the syntax of Vlookup;
Lookup_Value = Cell value whose value we need to find.
Table_Array = Range or table from where we need to find the value of lookup_value.
Col_Index_Num = sequence number of the column from which we want to get the value.
Range_Lookup = 0 (or FALSE) for the exact match and 1 (or TRUE) for the approximate match.
We will be seeing the use of TRUE and FALSE range_lookup value and will see how Vlookup True works in the below examples.Examples of VLOOKUP True
Lets us discuss the examples of VLOOKUP names.
You can download this VLOOKUP True Excel Template here – VLOOKUP True Excel TemplateExample #1
In this example, we will see the simple way to apply Vlookup True. For this, we have a list of alphabets, as shown below. As we can see, the alphabets are in proper sequence. And in separate cells, we will be looking up the values from the list and see the output.
In cell C2, we will put any of the alphabets we want to look up from Column A. Let’s consider it D.
In cell D2, insert the vlookup function and select the lookup value and range as needed.
To get the exact match, we can select FALSE range lookup or 0 for the exact match as shown below.
Once we enter, we can see that cell D2 will have the exact match value from the selected range.
Now, if we delete the Lookup Cell value D from the list available in column A, then Vlookup will not be able to look up anything and will return #N/A.
Once we exit the syntax, we will get the value nearer to the lookup cell value D in cell D2.
As we can see, the obtained value at cell D2 is C. This is because Vlookup TRUE gives the near about value if the exact match is unavailable. Here for the D, it returned the approximate match as C because C is the only near value before D.Example #2
In this example, we will see how Vlookup True works with a larger data set. Below is a table where the names of the different persons, ages, and physique types are mentioned from Columns A to C. And there is another small table in column E: F where we will map the value from table 1.
As we can see, we have age and physique types for each person. Now, let’s find out the physique type of any age, say 26. Insert the vlookup at cell F2 as shown below.
Select the lookup cell E2 and the Lookup range from B2 to C8, as shown below.
Now, if we randomly change the age from 26 to 53 in cell E2 and again apply the lookup using TRUE range lookup criteria and see what we will get? As of now, when we change the cell E2 value from 26 to 53, we got #N/A, as there is no value in column C available for age 53.
Now we will apply the Vlookup function using TRUE.
Once we press enter or get exited from the Vlookup syntax, our physique type in cell F2 is GENERAL, as shown below.
The reason for getting General in cell F2 is the TRUE range lookup in vlookup syntax. As we did not have the age as 53, so Vlookup True returned us the value as General because, before 53, we got general physique in cell B4.
If we again change the value, let’s say 31. Then we will get a value closer to 31 per the table. Here we have 30 in cell B2.Pros of VLOOKUP True
It helps us get the approximate match if we do not have the exact value for the lookup cell.
It is as simple as Vlookup False.
Using this function is far better than using different IF condition functions.Things to Remember
It gives an approximate match.
We can also use 1 in place of TRUE in the VLOOKUP syntax.
For the numerical value, It looks up the value less than the value available in the lookup cell.
Vlookup False gives the exact match, and if the match value is not available, then we will be getting #N/A
#N/A means the lookup cell’s value is not found in the entire lookup range.
The lookup range could be a table or column, but the value we will get from the column only.Recommended Articles
This has been a guide to VLOOKUP True. Here we discuss How to Use VLOOKUP True, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
Update the detailed information about Vlookup In Excel (Formula, Examples) on the Katfastfood.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!