Microsoft Excel’s VLOOKUP function is a more dynamic way of looking up data in the application. Thus, the user can find different items in a table or range by rows. For example: in an automotive parts inventory spreadsheet, you can search by part number to see information such as name, price, manufacturer, etc. With that, this function turns out to be quite important. Learn how to do VLOOKUP.
What is the VLOOKUP formula in Excel?
The VLOOKUP formula in Microsoft Excel is used to search for a specific value in a table or range of data and return a corresponding value from another column in the same row. The basic syntax of the VLOOKUP formula is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Where:
- “lookup_value” is the value you want to find in the first column of the table_array.
- “table_array” is the range of cells that contains the data you want to search, including the column that contains the lookup value and the column that contains the value you want to return.
- “col_index_num” is the column number in the table_array from which you want to return a value. The leftmost column in the table_array is column 1, the next column is column 2, and so on.
- “range_lookup” is an optional argument that can be either TRUE or FALSE. If set to TRUE or omitted, VLOOKUP will find an approximate match for the lookup_value. If set to FALSE, VLOOKUP will only find an exact match for the lookup_value.
For example, if you have a table with student names and their corresponding grades, and you want to look up the grade for a specific student, you could use the following VLOOKUP formula:
=VLOOKUP(“John Smith”, A2:B10, 2, FALSE)
In this formula, “John Smith” is the lookup value, A2:B10 is the range of cells that contains the data, 2 is the column number of the “grades” column in the range, and FALSE indicates that an exact match is required. The formula will search for “John Smith” in the first column of the range and return his corresponding grade from the second column of the range.
To help, Microsoft has summarized the formula arguments as:
=VLOOKUP(What you want to search for, where you want to search, the column number in the range that contains the value to return, returns an Approximate or Exact match – indicated as 1/TRUE or 0/FALSE).
How to Use the VLOOKUP Function in Microsoft Excel
- Insert the searched value cell into the formula: First, add the “=” sign and reference the cell that has the value you want to look up;
- Determine the table_array of your VLOOKUP: In this step, you need to inform the worksheet range where your result will be searched;
- Enter col_index_num: In this step, inform which column is what you want to search. Remembering that it starts to be counted from the first column that you put in matrix_table;
- If you want Excel to return the exact value you are looking for, enter 0 (or TRUE). If you are looking for approximate values, type 1 (or FALSE);
- Finally, you can use the formula to perform searches in your worksheet. You can just use the code for each item to find the results.
How to use VLOOKUP to pull data from other tabs in the same worksheet
- To start, select a column in one of the tabs that will be the result of your search;
- Locate the reference unit code and enter =VLOOKUP(looked-up_value;
- Then open another tab and select all the cells you want to use in the data search. Don’t forget to add the “;” in the VLOOKUP formula;
- Define the column that has the information to be returned;
- Press “Enter” and that’s it.
What to do when it does not work?
Even after following all the steps correctly, it is possible that something goes wrong. Don’t despair, there are ways to fix and tweak your spreadsheet.
Make sure the lookup value is in the first column given in the formula
It’s important to make sure the value you’re interested in is in the right place. VLOOKUP has a restriction that it can only look up values in the leftmost column in the table array. That is, ensure that the formula is referencing the correct column.
Inadvertently, we can end up typing something that shouldn’t be in the worksheet or in the formula itself. So, if the desired result isn’t coming up, it might be a good idea to check each cell for any hidden spaces or a non-printing character.
See if there is a value less than the entered search value
This can occur with the range_lookup argument when searching with the TRUE setting. If the #N/A error appears, the lookup value is less than the smallest value in the array. If you come across this glitch, just correct the number as needed.
Make the search column in ascending order
When using the VLOOKUP function and the TRUE argument, an error may arise if one of the lookup columns is not in ascending order. The first possible solution is to change the formula to FALSE as it does not require the use of a sort.
round big numbers
Because Excel has trouble storing large floating-point numbers, you have to shorten and round each one to five decimal places.
