How to Vlookup from Excel to Google Sheets (3 Suitable Ways)

We are going to use a Sales Report of a particular grocery store. This dataset concludes the Sales Rep, Order Date, Product Name, and the corresponding Sales amount in columns B, C, D, and E, respectively.

Method 1 – Find a Single Value

Step 1 – Import Excel File to Sheets

• Create a blank spreadsheet in Sheets.
• Go to the File tab.
• Select Import from the list.

The Import file dialog box appears.

• Move to the Upload tab.
• Click on Select a file from your device.

The Open window appears.

• Select the folder where you kept the file.
• Select the desired file.
• Click on the Open button.

The Import file dialog box pops up.

• Select Replace spreadsheet as the Import location.
• Click on the Import data button.

The dataset is available on the Sales Report Google Sheets spreadsheet.

Step 2 – Insert a Formula

• Copy the URL of the Sales Report sheet.

• Move to the new sheet named VLOOKUP from Excel to Google Sheet. We can see the columns of ID and names of the Sales Rep are already present. We just have to extract the corresponding Sales amount for each Sales Rep.
• Select cell D5 and enter the following formula.

C5 is the cell reference for Jonas, because we want his sales. The part inside the double quotes of the IMPORTRANGE function is the URL of the Sales Report sheet. This function helps to import data from another Google Sheet. 4 is the index of the VLOOKUP function as the Sales column is in the 4th position of the table in the Sales Report sheet. We used FALSE as the dataset is not sorted.

• Press Enter.

But we cannot see the proper results in cell D5. Rather, it shows a #REF error.

• Click on cell D5.
• This will show an error notification You need to connect these sheets.
• Click on Allow access in the message box.

Here’s the output in that cell, which is 6200.

• Bring the cursor to the bottom-right corner of cell D5 and it’ll look like a plus (+) sign. This is the Fill Handle tool.
• Double-click on it.

All the remaining cells get their outputs as the Fill Handle tool copies the iterated formula to the following cells.

Step 3 – Compare the Excel File to Sheets

The right one is the Excel file, and the left one is the Google Sheet.

Read More: How to Save Excel Files to Google Sheets

Method 2 – Fetch Multiple Values in the Same Column

Steps:

• Go to cell D5 and insert the formula below.

The VLOOKUP function will return the lookup value of all the sales reps in the C5:C14 range. We’ve added the ARRAYFORMULA function to get an array output.

• Hit Enter.

Method 3 – Apply the VLOOKUP Function with a Wildcard for Partial Matches

Steps:

• Select cell D5 and paste the following formula into the cell.

We’ve used “Jon*” as the search_key. The function will search for text strings containing these texts and will return the Sales amount.

• Hit the Enter key.

To extract the sales from Harry, edit the formula a little bit.

• Write down “Ha*” instead of “Jon*”.
• Press Enter.

Things to Remember

Before using the IMPORTRANGE function, make sure to change the privacy of the sheet from Restricted to Anyone with the link. Otherwise, it will return the #REF error.

Download the Practice Workbook