In this article, you will learn how to use the VLOOKUP formula in Google Sheets. This function is beneficial when you need to extract necessary information out of a table containing lots of irrelevant items and data. Its function is to search for a specific value in a column and return a value in a different column in the same row.
Generic formula
Search_key: This is a value based on which this formula looks for a specific item.
Range: This is the entire area within which this function looks for a specific item. The leftmost column should contain a particular keyword you enter as search_key.
Index: This is the position of a column within the range selected from which the formula pulls out requested information. For example, if the specified range is C5:E9 and you want to see the data in Column D, this index number should be 2, as Column D is the second column in the chosen field.
[is_sorted]: You need to choose “FALSE” for an exact match or “TRUE” for the closest match. You can leave this part blank but in this case it is taken as “TRUE”. So, you are recommended to enter “FALSE” to avoid pulling out incorrect data.
Learn how it works by looking at the example below. Assume you are a customer relationship manager, you have a set of data, Raw Data in the picture below, and you need to extract the revenue of an Eggplant company.
At the bottom part of the upper screenshot, you can see two “#N/A” next to the company IDs of “24” and “34”. This is because there is no figure that exactly matches them in the leftmost column in the selected range, specifically, B:19 through B:23, {10,20,30,40,50}. However, if you use “TRUE”, as you observe at the bottom part of the second screenshot below, values are returned by the formulas even if the company IDs of 24 or 34 do not exist in the raw data.
You may have a table containing copious data in a separate tab and want to make a summary table in another tab. You can refer to data on a different sheet by going there while you select “range” in the formula. Check this article if you don’t know how to go to another worksheet while selecting a range.
The major mistakes and causes of errors when you use the VLOOKUP are as follows:
If you are looking for an alternative method of the VLOOKUP formula, we can suggest you use a combination of INDEX and MATCH functions. If you have never tried the functions, you should learn them because they are more flexible than the VLOOKUP function. Check this page to understand how to use these functions.
If you don’t get the right answer to your question in this article or you have other questions on lookup formulas, you may find the answers in the following articles.
How to Use HLOOKUP in Google Sheets
XLOOKUP - Google Sheets: Explained
INDIRECT Function in Google Sheets: Explained
How to Use INDEX Function in Google Sheets
How to Use MATCH Function in Google Sheets
XMATCH in Google Sheets: Explained
OFFSET Function in Google Sheets: Explained
Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇