Vlookup feature is one of the most needed features for many sorts of calculations. With this feature, one can easily find the same value of a column navigated through a whole row. Now if that definition sounds nerdy and challenging, here is another way to explain what this feature does.
Suppose you went to an expensive restaurant and you were looking at their menu to order something good (but you don’t want it to be hard on the wallet either).
Then you start browsing the menu and you shift your eyes to the right to see the price of that dish when you find something that you want. Still, the people who were wondering or still puzzled how to vlookup in google sheets must follow the blog till the last word of it.
The tutorial discusses the Google Sheets VLOOKUP function syntax and demonstrates how to use Vlookup formulas to solve real-life tasks. One of the most common problems when dealing with interrelated data is locating data across multiple sheets.
In daily life for example, when checking a flight timetable board for your flight number to get the departure time and status, you also perform certain tasks. A common view is that VLOOKUP is one of the most complicated and obscure features.
5 Things to Note About VLOOKUP’s Google Sheets
The Google Sheets VLOOKUP feature, as you have already understood, is a thing with complications. It will keep you out of trouble by recalling these five basic facts and prevent you from getting the most frequent Vlookup errors.
-VLOOKUP can’t look to the left of Google Sheets, it only looks in the first (left column of the range. Use the Google Sheets Index Match formula to run a left Vlookup.
-Vlookup is case-insensitive in Google Sheets, which means that lowercase and upper case characters are not differentiated. Using this formula for a case-sensitive lookup.
-Set the is sorted argument to FALSE to return exact matches if VLOOKUP shows inaccurate results.
-Recall that the first column of the range is sorted in ascending order when set to TRUE or omitted. In this case, a faster conditional search algorithm will be used by the VLOOKUP feature that only operates perfectly on sorted data.
-Google Sheets VLOOKUP enables partial matches to be searched based on wildcard characters: question mark (?) and asterisk (*). Please see this example of the Vlookup formula for more information.
How to Apply Google Sheets vlookup Formula?
As I continue, you can also operate.
First of all, you can open and type docs.google.com in your browser. From then login to your own account and click the options menu in the top left corner and choose Google Sheets, where you can create a new sheet with the same interface as Excel using the templates provided.
Or by importing the previously generated document from your computer.
So on the left side of the sheet, I uploaded the document that has master data table no. 1 indicating the code, name, department and salaries of the employees working in the same organization.
We have table no. 2 on the right side with only the code and salary issued. Suppose we need to find out the salary for a specific code that is included in the 1st table as well. In this scenario, we have to provide the same salary value right next to the code that has already been provided.
To shorten our headache, here we add the vlookup. Vlook up has only 4 arguments, the first of which is search key, range, index, and last one (is sorted). In the entire operation of the formula, these arguments are nothing but the conditions that will follow.
Search key-The meaning or object you are searching for is this. That will be a burger or pizza, for example in the case of a restaurant.
Range-this is the range to be used in the feature Vlookup. For the search key, the left-most column of this set will be scanned.
Index-this is the column number from which you want the result to be obtained. In the set, the first column is 1, the second column is 2, and so on.
Is-sorted-[TRUE by default]-you can decide whether you are looking for an exact fit or an indicative fit in this statement.
For an exact fit, you might use FALSE, and for an indicative fit, TRUE. If you use TRUE, the list must be ordered in ascending order. If you do not indicate a value here the default is TRUE.
Step 1-We need to use the unique employee code as the search key first and foremost.
Step 2-The master data is selected as our range.
Step 3– As the salaries given are provided in the 4th column of the table, we need to write 4 for the index.
Step 4– We type 0 or false for the exact match, then close the bracket and press enter all that.
Note: The preview is shown in a small dialog box after completing the correct formula, showing the value to be returned.
Step 5– In addition, if we need the salary values for other employee codes, we only need to pick and drag down the formulated cell as needed to fill the necessary data automatically.
Another Google Sheets method for advanced search is multiple VLOOKUP Matches. As its title suggests, as the VLOOKUP feature does, the add-on will display all matches, not just the first one.
In addition, it can analyze several conditions, look up in any direction, and display all or the number of matches listed as values or formulas.
Remembering that an image is worth a thousand words, let’s see how real-life data performs on the add-on. Supposing, there are several items in certain orders in our sample table, and you want to retrieve all items from a particular order. A Vlookup formula, although a more efficient QUERY function can, is unable to do this.
The issue is that this feature requires knowledge of the query language, or at least the syntax of SQL. Do you have the urge to spend days learning it? Install the add-on for Several VLOOKUP Matches and get a faultless formula in seconds!
Also Read: Top 7 Best Money Transfer Apps In 2020
Now that is enough for the users who were researching how to vlookup google sheets.
Thanks for having a nice day, signing off Sanjay.