Using a spreadsheet to maintain data is a great way to stay organized. Spreadsheets can aid in recording budgetary expenses, planning a vacation, or tracking invoices for business purposes. It is not uncommon to be overwhelmed by the limitless functions of an Excel sheet and not know what tools to use to solve your problem!
In this article, we will be looking at how to use the VLOOKUP function. VLOOKUP is a helpful tool for retrieving many data types in one step.
Simple Example – A Product Database
Let’s work through an example of tracking inventory. Perhaps you want to reflect the product’s name in column A, the product type in column A, the price in column C, and how many you have sold in column D.
After you have typed “=VLOOKUP” into the cell, a list of variables will appear underneath. These options are lookup_value, table_array, col_index_num, and range_lookup. The variables listed are a set of inputs required to perform the data search.
The lookup_value in the formula finds an approximate data match within a set of corresponding values. For this option, you must insert the value that you would like Excel to search. For example, it might appear as “=VLOOKUP(“Top Gun”…)”.
The next item we must input into our formula is the table_array. The table array encompasses the entirety of the selected range of cells and reads all of the information selected to search in the VLOOKUP process. Think of the table array as an overview of data that the system has to sort. In this example, the table array is range A2 to D6. You do not need to include the header row in the range.
Interesting read – How To Unsort In Excel
Column Index Number
Column index number (col_index_num) deals with the column you’re searching. For example, if you want to return the Unit Price you would use column “3”.
The final variable is the range_lookup. The range lookup is optional – you need to choose between TRUE or FALSE. More on the TRUE and FALSE options below. The default value will be TRUE if you don’t choose an option.
TRUE lookups will return data that will give only an approximate answer. For example, if you use the TRUE argument in VLOOKUP to return data concerning an order number, TRUE will answer with approximate order numbers.
False lookups, on the other hand, will retrieve an exact answer. In the example of sorting through order numbers, the FALSE argument would return only exact order numbers. FALSE retrieval is optimal for precise value searches.
Bringing It All Back Together
Ok – you now know what each variable does. Let’s bring the example to life. Say you want to search the unit price of the “Top Gun” movie.
- Lookup Value – “Top Gun”, alternatively if you want to make the lookup more dynamic, you can search an input cell with the product names and link to that.
- Table array – $A$2:$D$6 which is the data table less the heading row which is not needed
- Column Index – We want the unit price, so it’s the third column
- Range lookup – optional here as column A doesn’t have any similar products. You can put in FALSE for completeness.
Interesting read – Copy Paste Like A Pro In Excel
Limitations Of VLOOKUP
There are a few limitations of VLOOKUP.
- The lookup value needs to be in the first column – so you cannot look left of the table
- VLOOKUP can only search one criteria. For example, if you had several staff members named Bob, you need to create a new key with the staff ID or surname to create unique identifiers for Excel to search
- VLOOKUP can be very slow on large data tables – Excel effectively stores several instances of the same data table to complete the searches
Personally, I don’t use VLOOKUP that often anymore. I prefer to use INDEX+MATCH and I’ve written a guide here – What Is Index Match? A Simple Step By Step Guide. It’s much more flexible and dynamic so check it out if you’re struggling with the VLOOKUP!
Thanks for reading and I hope you found this helpful.
Other Excel tutorials you may also like: