Dizzy thinking about the correct VLOOKUP formula? Jaka has this guide on how to use VLOOKUP on Ms. The most complete Excel plus examples and pictures.
The Excel VLOOKUP formula must be familiar to some of you, right, gang?
Given one of the most important criteria in mastering the skills of Ms. Excel is the ability to use Excel formulas. Where one of them is VLOOKUP formula/formula.
This formula is very often used in various jobs, especially jobs related to administration and finance.
Even so, it turns out that there are still many who don't know how to use VLOOKUP on Ms. Excel or Google Sheets. You too? If so, let's see the discussion on how to quickly learn the following VLOOKUP formula!
Excel Vlookup Formula Functions
The VLOOKUP formula or formula is a formula used for search and match specific values in the two tables.
With this formula, you can get the value of a certain data automatically. For example, you can fill in the amount of employee salaries automatically only based on their class.
In the example above, this formula will match the employee class with the salary data that has been described in another table.
To be clearer in understanding how Excel VLOOKUP does the calculation, let's look at the examples of the Excel VLOOKUP formula that ApkVenue has prepared below.
How to Use Excel VLOOKUP Formulas in Microsoft Excel
Before moving on to usage examples, you must first understand the components of the Excel VLOOKUP formula itself.
In general, the VLOOKUP formula looks like =VLOOKUP(Reference Value;Table;th Column;True/False).
Pay attention to the table below. To find the amount of salary for each person automatically, we will compare the data on the group of people in Table B with the data on the amount of salary in table A.
By looking at the 2 tables above, the following is an explanation of the components of the VLOOKUP formula in the case of matching salaries.
Reference Value - Reference value is the same benchmark value between 2 available tables. In this case it is the employee class.
Data Table - Data table is a table that contains comparative information. In this case the data table used is table A (A4:C6).
Column to- - What is meant by Column to- is the column where the desired data is in the data table. In this case, column 1 = Group, column 2 = salary, and column 3 = Bonus.
True/False - True/False is a function in the Excel VLOOKUP formula. Use False if the value you are looking for is an exact match, and use True if you want to find a value that is close to or not exactly the same.
The following is an example of how to use Excel's VLOOKUP using the function True and also false.
Example of an Excel VLOOKUP Formula with Exactly the Same Value and How to Use It
In this example, we will try to fill in the salary of each employee based on their class using the Excel VLOOKUP formula.
There are several steps you must take to find the salary amount automatically using VLOOKUP, and here are the complete steps.
Type =VLOOKUP( in the salary column.
Click the column for the employee group you want to fill in the salary and add a;. For column J4, it appears that the VLOOKUP formula will be =VLOOKUP(I4;
The data block in table A.
Insert a $ sign in the number and column number so that it can bedrag down, and mark ;. The formula will look like =VLOOKUP(I4;$A$4:$C$6;.
Add numbers 2 and also ; in the formula, so that it becomes =VLOOKUP(I4;$A$4:$C$6;2. This is because what will be searched is the salary, and the salary is in the 2nd column in table A.
Add False, close the formula with a sign ) then press enter. The final display of the formula becomes =VLOOKUP(I4;$A$4:$C$6;2;False).
With this you can directlydrag This formula goes all the way to the bottom to find the other employee's salary values.
You can use this VLOOKUP formula in other cases by paying attention to the rules that Jaka explained earlier.
Make sure that the reference value is written vertically in the comparison table (Table A), and the value you want to enter is written horizontally from the reference value in table A.
Excel Vlookup Formulas and Examples with Closer Values
The second example is an example of how to VLOOKUP in Excel to look for values that are close or not exactly the same.
Usually this is used to determine the group of values of a certain number of quantities. The example that Jaka uses this time is to find the final grade of a student based on the points collected.
There are 2 types of tables that ApkVenue uses for this example. Table A is the reference value based on the points earned and Table B is the number of points collected by the students.
Here are some steps you can follow to create a VLOOKUP formula with close values.
Type =VLOOKUP( in the final value column.
Click the number of points earned by the person and add a sign ;. The formula display will be =VLOOKUP(H4;.
Table block used as a reference value.
Insert sign $ in letters and numbers so that they can bedrag down. Add sign ;. Formula display =VLOOKUP(H4; $A$4:$B$7;
Add numbers 2 and also sign ; into the formula. This is because the value is in the 2nd column. The formula display becomes =VLOOKUP(H4;$A$4:$B$7;2
Add TRUE and close the VLOOKUP formula. Formula final display =VLOOKUP(H4;$A$4:$B$7;2;TRUE).
This way, Excel will automatically round to the nearest set of values and determine the most suitable value.
Make sure that the range of values in the comparison table is written from smallest to largest, because this formula will look for the most suitable value from top to bottom.
How to Use Vlookup with Different Data Sheets
Already have a reference table before, but the table is in sheet different? Don't worry, the Jaka gang will discuss how to use the VLOOKUP formula for different sheets in Microsoft Excel too.
This way you don't have to bother to copy back the tables that were previously created, and your work will look much neater.
Here are some steps you can take to create a different sheet VLOOKUP formula. Jaka uses the example table in the previous segment to make it easier.
Write the VLOOKUP formula as usual until the reference column is located. The formula display becomes =VLOOKUP(H4.
Add Sheet4! before blocking the reference table or in this case table A. Because Jaka moved table A to sheet 4.
Block table A as before and insert a sign $ so that it candrag later. The formula display will be =VLOOKUP(H4;Sheet4!$A$5:$B$8;
Add 2 and TRUE to the formula. The final display of the formula becomes =VLOOKUP(H4;Sheet4!$A$5:$B$8;2;TRUE).
Notes:
The addition of the numbers 2 and TRUE because the value column is 2nd column and the value you are looking for not exactly the same.
This way you can already use the VLOOKUP formula for different sheets. You just need to adjust again to the work cases you face.
Example of How to Use Vlookup Formulas with Different Files
Not only can it be used for data on sheet different, VLOOKUP can also be created using data from different excel files.
There are a few different steps to apply how to use VLOOKUP in this different file by using the usual VLOOKUP formula.
To better understand how to use the VLOOKUP formula in different excel files, here is an example of its use in the same case as before.
- Write down the VLOOKUP formula as you normally would to the reference value points. The formula display will be =VLOOKUP(H4.
- Open the excel file where the comparison table file is located and the data block to be used. The formula display will be =VLOOKUP(H4;'[VLOOKUP DATA.xlsx]Sheet1'!$A$5:$B$8
That's how to use the VLOOKUP formula in different Excel files. This way, you can manage the data in your work more easily. That's a series of ways to use Excel's VLOOKUP formula with various functions and also examples. Even though it's a bit complicated, with the explanation that ApkVenue provides, I hope you can apply it framework this formula in various situations. Hopefully the information that ApkVenue shares this time is useful for all of you, and see you again in the next articles. Also read articles about Apps or other interesting articles from Restu Wibowo.Notes:
