Pull Data into Excel Reports from Another Spreadsheet with VLOOKUP and XLOOKUP

How to Use VLOOKUP and XLOOKUP functions to Extract Data in Excel

Excel is a powerful tool for managing and analyzing data, and one of its most useful features is the ability to retrieve specific information from one sheet and display it in another. In this tutorial, we'll walk you through the steps of using two popular Excel functions, VLOOKUP and XLOOKUP, to pull data from another spreadsheet. Watch the video to see VLOOKUP and XLOOKUP in action. Whether you're a beginner or an experienced Excel user, you'll find this guide helpful in simplifying your data retrieval tasks.

How to pull a column of data into an Excel report from another spreadsheet using VLOOKUP and then XLOOKUP functions. Extract data from one workbook to another.

Step 1: Prepare Your Spreadsheets

Before you begin, ensure that you have your two Excel spreadsheets ready and open. The first sheet contains the data where you want to display the results, the second sheet has the column of data you want to retrieve. In our example below, we are going to pull a column of data (manager name) from one spreadsheet and display it in another workbook (employee listing), and VLOOKUP and XLOOKUP will ensure the correct manager's name is displayed by comparing the Employee ID numbers in both sheets.

Step 2: How to Use VLOOKUP

VLOOKUP is a widely-used Excel function for data retrieval.

In the first sheet, select the cell where you want the retrieved data to appear. This will be your target cell.

Type the following formula in the target cell:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value is the value you want to search for in the first column of the first sheet.

• table_array is the range of cells where Excel should look for the data, including the column containing the value you want to retrieve.

• col_index_num is the number of the column in the table_array from which you want to retrieve data.

• [range_lookup] should be set to FALSE to ensure an exact match.

Replace lookup_value, table_array, and col_index_num with the appropriate cell references or values from your spreadsheet.

Press Enter. Excel will display the retrieved data in the target cell.

To apply the formula to multiple cells, simply copy and paste it to other cells in the column.

VLOOKUP Function in Excel in Action

Step 3: How to Use XLOOKUP

In the first sheet, select the cell where you want the retrieved data to appear, just like in Step 2.

Type the following formula in the target cell:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value is the value you want to search for.

• lookup_array is the range where Excel should search for the lookup_value.

• return_array is the range from which you want to retrieve data.

• [if_not_found] is optional and specifies what to return if no match is found.

• [match_mode] and [search_mode] are optional and allow you to fine-tune your search.

Replace lookup_value, lookup_array, and return_array with the appropriate cell references or values from your spreadsheet.

Press Enter. Excel will display the retrieved data in the target cell.

To apply the formula to multiple cells, copy and paste it as needed.

XLOOKUP Function in Excel in Action

So, which function should you use?

It really depends on your specific situation, your level of comfort in Excel, and the complexity of the task you need to accomplish. VLOOKUP is a great function for finding related information in large tables or when you want to add a column of data to a report, which is what we are going to do in this tutorial.

However, VLOOKUP does have its limitations. For example, it only works to the right of the lookup value, so you can't use it to look up values to the left of the lookup column. Additionally, VLOOKUP can only search for exact matches or approximate matches based on the closest value, which can be problematic if you have duplicate values.

XLOOKUP is a more powerful and flexible function than VLOOKUP that allows you to search for values in any direction (left, right, up, or down) and also supports fuzzy matches and wildcards. It returns an exact match by default and doesn’t require you to use a column index number.

Conclusion

In this tutorial, we've covered how to use both VLOOKUP and XLOOKUP in Excel to retrieve data from another spreadsheet. Whether you prefer the traditional approach of VLOOKUP or the more versatile capabilities of XLOOKUP, these functions can greatly simplify your data retrieval tasks. Experiment with both functions to see which one best suits your needs, and remember that practice makes perfect when it comes to Excel!

Be sure and check out how to automate your reports in Excel with Office Scripts…it will save you so much TIME!