The vertical lookup tool is one of the most valuable functions used in spreadsheet programs like Google Sheets and Microsoft Excel for individuals with vast data. One can use the Vlookup function to find information or excerpt certain information vertically hastily. However, not all features available in Excel’s Vlookup are in Google Sheets Vlookup, such as functionality and syntax.
If one is using Vlookup for the first time, it might take a while to understand how it works, especially if it involves two programs simultaneously. However, there are several methods to use Vlookup, from Excel to Google Sheets, since some features differ. If you are wondering how the processes work, you are in the right place; stick around to learn more.
This post details a lot about the different procedures used for Vlookup, from Excel to Google Sheets, step-by-step guides, examples in the form of images, and so forth. Follow along, and you will be able to perform Vlookup from one program to another in no time.
Overview of Vlookup
Dealing with large chunks of data is gruesome and hectic, especially when using spreadsheet programs such as Microsoft Excel or even Google Sheets. Still, through the help of the function Vlookup, you can search for values quickly when done correctly in a table or specific range within a data set, also allowing you to retrieve information from corresponding cells in the same row. It is important to note that the lookup value must be located in the leftmost column of the table_array.
One can use VLOOKUP in various scenarios. For example, it can be used to retrieve employee information based on their ID number, fetch product details based on a product code, or extract sales data for a specific month from a larger dataset, making it an undoubtedly potent tool, especially when working with large and diverse quantities of data, because it allows you to retrieve specific data and enables you to merge datasets reliably. Also, it makes it less tiring and more effective when performing simple data analysis.
Another essential feature of VLOOKUP is its ability to perform approximate matches using range_lookup. It is beneficial when dealing with numerical data, such as finding the closest match to a target value within a range. Vlookup also comes with some limitations that an individual needs to understand to evade errors and generate correct outcomes. For instance, Vlookup only works vertically in that you cannot recover information from the left to the right lookup.
Vlookup Function Syntax
Vlookup is the short form of Vertical Lookup. This implies that the function looks for values that are up or down a column, and it looks like this
: =VLOOKUP(search_key, range, index, [is_sorted])
To use this syntax, you should first understand each parameter in the Vlookup function, as explained below, for it to work.
- Search_key: the search key is always the maximum value when building up your Vlookup formulae and represents the values you will be looking for in the lookup table. Through that search_key, you can retrieve the corresponding information along the same row. The Search key must appear in the leftmost column of the table.
- Range: this represents the area you want to search, and it helps you control or limit the extent of your search to only the desired and most relevant values. The range must include the lookup value and the columns from which you expect to retrieve the result.
- Index: The column from which the result value is selected as a result of the lookup value is determined by the column index number selected in the syntax. Each column to the left of the primary_key column can be used as the index value.
- Is_sorted: this is a logical value that takes the form TRUE or FALSE as the last parameter of the Vlookup syntax. This parameter lets you specify whether you want an exact value or an approximate or close-to match.
Is It Possible To Vlookup From Excel To Google Sheets?
Yes, it is possible. This is all enabled by Google Sheets when it comes to syncing and working in teams. Google Sheets has proved to be one of the best at this because of its amazing, reliable, collaborative features. Google Sheets allows the convenience of extracting data from Excel and putting it in Google Drive, or you can also directly just upload from Excell to Google Sheets.
It is important to note that when performing a VLOOKUP from Excel to Google Sheets, both files need to be accessible and stored in a location that Google Sheets can access. Additionally, any changes made in the Excel file will not automatically update in Google Sheets. Instead, you will need to manually refresh the formula or reapply it if any changes occur in the Excel file.
There are various methods to Vlookup, from Excel to Google Sheets, and we will demonstrate them in-depth in this post. These methods will make you more productive and better in your work and project environment.
For better understanding, we will use the example below of a sales report Excell Workbook to take you through the approaches used to Vlookup from Excel to Google Sheets.
Finding a Single Value
We can use the Vlookup function to locate a specific single value in a data set by fetching only one value at a time.
Step 1:Import the Excel file to Google Sheets
The very first step you should take is importing your Excel file to Google Sheets to enable you to use the Vlookup function that allows you to isolate a single value available on Google Sheets.
Begin with creating a blank spreadsheet in Google Sheets near the top left corner. Click on the File tab at the pop-up, and select Import from the list.
Once you have clicked on Import, a dialog box will appear on your screen; afterward, you will move to the upload tab and choose Browse to select a file from your device.
Another window will appear on your screen, called the Open window. Through this window, you first select the location of your file, whether it is located on the desktop, documents, downloads, etc, depending on where you stored your Excel spreadsheet. For instance, using the example below, we kept it in the Desktop folder.
Once you locate the folder, select the Excel file you want to use and click on the Open button, as demonstrated below.
For another time, a dialog box titled Import file will pop up after you press open. In the middle left side of the pop-up, you are presented with a drop-down list under Import location, where you need to select “Replace Spreadsheet” and then click Import data.
One will successfully import the data set from Excel to our Sales Report spreadsheet in Google Sheets.
Step 2: Slotting in Formula
Having already transferred your Excel spreadsheet to Google Sheets, you can now proceed to the second step, where you will do the main job of this process which is constructing the Vlookup formulae using the IMPORT RANGE functions available in Google Sheets.
Note: Before beginning the second step, ensure that you copy the Sales Report URL on your Google Sheets as highlighted below.
Following our example, we move to a new spreadsheet on our Google Sheets. Here, you will see columns of Names of Sales Representatives and the number of sales made by each representative, where the sales representatives’ names are already present.
Henceforth having both spreadsheets, you now have to extract the specific Sales amount that corresponds to each Representative from the Sales Report spreadsheet. Select cell B2. This is where you will write the following VLOOKUP formula.
=VLOOKUP(A2,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1jKDxF2U4OQMiGnbLPlNavkDssC7BgiwFBaOJrFTsCrk/edit?usp=sharing“; “sheet1!A1:C5”),3, FALSE)
From the above formula, B2 is the cell in which we want the value corresponding to the sales made from the Hats product. Next is the IMPORT RANGE function, with the part inside brackets and double quotes being the Sales Report sheet URL which helps import data from other Google spreadsheets. This is then followed by 2 giving index to the Vlookup function since the sales column is in the second position of the Sales Report sheet table data. Finally, we used False because we want an accurate result and remember that the dataset is not sorted.
Always make sure the function is written and punctuated correctly, and then press Enter.
After taping Enter, you cannot see the correct result in cell B2; instead, you will receive a #REF error, which should not worry you. Click again on cell B2, and it will immediately display an error notification asking you to connect these spreadsheets to give access to the Sales report Sheet. Finally, Click on Allow access, and you will now see the correct expected value.
N/B: Using this formula returns one value at a time, although we can find the number of Sales made by the other Sales Representatives by changing the search_key parameter.
To find the Sales made by Mark, use A5 instead of A2 in the Vlookup formula.
Bring your pointer to the bottom right corner of cell B2, which will turn it into a plus (+) symbol called the Fill Handle tool. When double-clicked, this tool will copy the formula and apply it to the empty cells below, giving each its corresponding value.
Step 3:Compare Google Sheets file to Initial Excel File.
To verify whether the formula worked correctly, you will have to compare the files side by side.
Finding Multiple Values in a Column
This method is very similar to the first one for fetching single values. However, there is a slight change in the formula that will allow you to produce your output as an array of values. We will use the same Data from the Previous Method.
Begin by going to cell B2 and type in the following formula:
=ARRAYFORMULA(VLOOKUP(A2:A5,IMPORT RANGE(“https://docs.google.com/spreadsheets/d/1jKDxF2U4OQMiGnbLPlNavkDssC7BgiwFBaOJrFTsCrk/edit?usp=sharing“,”sheet1!A1:C5”),3, FALSE))
When you use this formula, you will be able to retrieve all the sales values made by each Sales representative all at once. This means that you will not be required to use the Fill Handle tool in this case.
In this method, you will note that the formula above has still used the IMPORT RANGE function because that is what will allow us to use the data from the Google Spreadsheet we imported from Excell.
The main difference between the first and second methods is the use of =ARRAYFORMULA, which helps to turn the original= VLOOKUP formula into an Array, .which, in turn, allows you to write only one formula that is used across multiple rows.
Applying VLOOKUP Function With Partial Matches or Wildcard
Using Inadequate matches is the third and final method you can use to apply VLOOKUP in your Google Sheets file. One can use the asterisk (*) symbol as a wildcard character. This will allow you to search for values that partially match the lookup value, as demonstrated below.
Start this method by selecting cell B2 and typing in the following formula.
=VLOOKUP(“Jam*”,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1jKDxF2U4OQMiGnbLPlNavkDssC7BgiwFBaOJrFTsCrk/edit?usp=sharing“, “sheet1!A1:C5”), 3, FALSE)
Above, we have used “Jam*” as the Search_key. This will make the function search for strings of text that begin with the characters J-o-n, and then it will return the corresponding Sales amount. Press the enter key to initiate the function in cell B2.
To Extract Sales made by another Sales representative, you only need to edit the formula just a little. For the search_key section, change from “Jam*” to “Ma*” and then hit the Enter button. This will make the Formula retrieve the Sales value corresponding to the Representative whose name begins with the characters M-a who is Mark, as we have demonstrated below.
=VLOOKUP(“Ma*”,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1jKDxF2U4OQMiGnbLPlNavkDssC7BgiwFBaOJrFTsCrk/edit?usp=sharing“, “sheet1!A1:C5”), 3, FALSE)
Points To Note
When performing Vlookup from Excel to Google Sheets, you should always be aware that the syntax function is slightly different between the two types of spreadsheet and should be used respectfully.
It is highly recommended to define named ranges, especially when using Google Sheets, as this makes it easier for you to refer to a specific range of cells which can also be used as table arrays in the long run.
Both Excel and Google Sheets handle data types differently, and this can affect VLOOKUP outcomes. Hence it is important for you to always format the data appropriately in both Google Sheets and Excel.
You are recommended to always thoroughly test your Vlookup Results after shifting from Excel to Google Sheets to ensure they are accurate. There might be some compatibility issues because of the difference in formulas and features in the two types of spreadsheets.
Finally, whenever you encounter an error, it is essential for you to double-check the formula and try troubleshooting them. Both spreadsheets may return a #N/A error implying the lookup value has not been found in the table array or other errors such as #REF! Or #VALUE! Meaning that there are issues with the formulae syntax or the data types.
Conclusion
Using the VLOOKUP function in Google Sheets, which is similar to Excel, can be done easily by following a few simple steps. Let’s break it down for beginners.
First, let’s understand the basic idea behind the VLOOKUP function. It allows you to search for a specific value in the first (leftmost) column of a table and retrieve a corresponding value from a specified column. This can be really helpful when you need to find and retrieve information from a large dataset.
To transfer the VLOOKUP function from Excel to Google Sheets, follow these steps:
- Open the Google Sheet where you want to use the VLOOKUP function.
- Locate the cell where you want to enter the VLOOKUP formula.
- Start by typing the equal sign (=) in that cell. This tells Google Sheets that you’re entering a formula.
- After the equal sign, type “VLOOKUP” (without quotes), which is the name of the function.
- Next, specify the search key or value that you want to look up.
- Then, specify the range of cells that contains the table you want to search. This is where you’ll find the desired value.
- Make sure to include the column index number of the desired value you want to retrieve. This tells Google Sheets which column to look for the value in.
- Finally, indicate whether you want an exact match or an approximate match. This depends on your specific needs.
It’s important to note that Excel and Google Sheets might have some differences in how they handle data formats. To ensure accurate results, make sure that values like dates, numbers, and text are formatted consistently across both platforms. This will help you avoid any discrepancies in the outcomes.
To make the transition smoother, we recommend practicing using the VLOOKUP function in Google Sheets before fully migrating from Excel. Create sample tables and experiment with different scenarios to familiarize yourself with the process. If you encounter any issues or have questions, you can find valuable advice and troubleshooting tips in online tutorials and forums.
In conclusion, transferring VLOOKUP functions from Excel to Google Sheets is a relatively straightforward process. By understanding the syntax differences and following the steps outlined above, along with practicing with sample data, you’ll be able to successfully use the VLOOKUP function in Google Sheets.