Before we walk you through how to use VLOOKUP in Excel, there is something you need to hear. Do you know that VLOOKUP is a powerful function in Microsoft Excel? It might interest you to know that VLOOKUP is used to search for a specific value in a table. Also, it returns a corresponding value from the same row.
But then, one may wonder, what does ‘VLOOKUP’ stands for? Is it a word, an abbreviation or an acronym? Well, VLOOKUP stands for “Vertical Lookup“. And it is one of the most commonly used functions in Excel. Now, as promised, let’s walk you through how to use VLOOKUP in Excel. Also, you will be guided on which step to take at a time.
How to Use VLOOKUP in Excel: Simple and Easy Steps
The actions to take in order to utilize VLOOKUP in Excel are listed below:
1. Get Your Data Ready
This is the first step to take when making use of VLOOKUP. You must prepare your data before using VLOOKUP. Also, you will need two pieces of data. The values you want to search for are in the first set of data you need. And the values you want to return are in the second set of data you will need. The next thing to do is to verify that the table’s leftmost column has the information you wish to search for.
2. Enter the VLOOKUP Function
This is the second move you must make. To use VLOOKUP, you need to enter the function in a cell. The basic syntax of the VLOOKUP function is as follows:
• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Lookup_value: This is actually the value you want to look up. It can be a cell reference or a value.
• Table_array: This stands for the range of cells that contains the lookup value and the corresponding value you intend to return. However, ensure you include all the columns in the table.
• Col_index_num: This is the number of columns in the table that contains the value you want to return. The leftmost column is column 1; the second column is column 2, and so on.
• Range_lookup: This is an optional argument that specifies whether you want an exact match or an approximate match. You are to Enter FALSE for an exact match or TRUE for an approximate match. But if you leave this argument blank, Excel will assume an approximate match.
3. Examine the VLOOKUP Function
The next step to take is to examine the VLOOKUP function. Once the VLOOKUP function has been inserted, you can test it by inserting a value in the table’s leftmost column. The same row’s value from the table should be returned by the VLOOKUP function.
4. Replicating the VLOOKUP Function
Once the VLOOKUP function has been tested, you may duplicate it to multiple cells in the same column to look up more data. However, the cell references in the VLOOKUP function must be changed to reflect the position of the new data.
5. Resolving VLOOKUP Issues
When faced with VLOOKUP issues, there are various things you may examine. If VLOOKUP is giving you errors, the first thing to do is to confirm that the lookup value is in the table’s leftmost column. Second, make sure that all of the table’s columns are included in the table array. Last but not least, confirm that the col_index_num identifies a valid column in the database.
How to do a VLOOKUP between two Spreadsheets
To do a VLOOKUP between two spreadsheets, you may need to seek up data from one spreadsheet and utilize it in another. And this is if you often deal with spreadsheets. VLOOKUP enables you to look up a certain value in one table and return a comparable value from a different table. Let’s go through how to do a VLOOKUP between two spreadsheets in this tutorial.
1. Open the two spreadsheets first.
2. Determine the lookup value
3. Open the second spreadsheet
4. Insert the VLOOKUP formula. How do you do this? In the second spreadsheet, enter the VLOOKUP formula in the first cell of the blank column. The VLOOKUP formula has the following syntax:
- =VLOOKUP(lookup_value, table array, col index number, [lookup_range]).
- The value you wish to search up is lookup_value.cThe first spreadsheet’s table_array refers to the set of cells that have the lookup value and the information you wish to retrieve.
- col_index_num: This is the index number of the table_array column containing the information you wish to obtain. 1 is the first column from the left, 2 is the next, and so on.
- ange_lookup: You may specify whether you want an exact match or an approximation of a match using this optional input. Enter FALSE or 0 for a precise match if desired. Enter TRUE or 1 for an approximate match.
- The VLOOKUP formula would be =VLOOKUP(A2, ‘[First Spreadsheet.xlsx]Sheet1’, for instance, if your lookup value was in cell A2 of the second spreadsheet and you wanted to obtain data from columns B and C of the first spreadsheet.A2:C100, 2 (FALSE)
However, it’s important to keep in mind that you must define the range of cells in the first spreadsheet that has the lookup value and the associated data.
5. Transfer the formula to more cells.
In summary, Excel’s VLOOKUP function is a strong tool that lets you look for a certain value in a table and return a comparable value from the same row. Now you know that you may use VLOOKUP to speed data analysis and increase the effectiveness of your work in Excel (even on an online Excel sheet) by following the instructions above. Also, it is necessary to state that you can master Excel with practice and utilize VLOOKUP to enhance both your professional and personal lives.