Function Problems

How to Use Excel Formulas to Pull Data from Another Sheet by Criteria

Phi Hung Vo Oct 21, 2026 868 views

Question details

The user needs a reliable formula to pull data from a different spreadsheet based on specific criteria.

How to Pull Data from Another Sheet by Criteria in Excel

Product: Spreadsheets
Device & OS: not provided
Scenario: Extracting and matching data across multiple sheets or tables to consolidate information.
Observed behavior: A standard cross-sheet VLOOKUP may return duplicate or incorrect results for every row due to incorrect relative range references or enabling approximate matching.

Before you start

Ensure that the source sheet contains no duplicate lookup values that could cause incorrect matches, and note down the exact sheet names and data ranges you intend to reference.

Solution 1Recommended

Use VLOOKUP with Exact Match and Absolute References

This is the most straightforward method to pull data from another sheet by ensuring your formula specifically looks for an exact match and keeps the lookup range locked.

When referencing another sheet, failing to lock your data range can cause the range to shift as you drag the formula down. This results in the same or incorrect data being returned. Using absolute references (adding $ signs) and setting the match type to FALSE solves this.

Select the destination cell

Click on the cell in Sheet1 where you want the pulled data to appear.

Enter the VLOOKUP formula

Type the formula: =IFERROR(VLOOKUP($G3,Sheet2!$G$3:$K$13,5,FALSE),""). Replace 'Sheet2!$G$3:$K$13' with your actual source data range.

Lock the references

Ensure you have dollar signs ($) in your lookup range to create an absolute reference so it won't shift when copied.

Apply to other rows

Press Enter, then drag the fill handle at the bottom-right of the cell down to apply the formula to the rest of the column.

Use VLOOKUP with Exact Match and Absolute References

Tip: The 'FALSE' argument guarantees an exact match. The 'IFERROR' function wraps the formula to return a blank cell ("") instead of an ugly #N/A error if the data is not found.

Solution 2

Use INDEX and MATCH for Flexible Data Pulling

INDEX and MATCH provide better flexibility, especially if the return column is to the left of the lookup column or if data is spread across multiple columns.

Advanced Spreadsheet Functionality

Seamlessly Pull and Analyze Data Across Sheets with WPS Spreadsheet

WPS Spreadsheet fully supports VLOOKUP, XLOOKUP, and INDEX/MATCH functions. You can easily pull, reference, and calculate data across multiple sheets with an intuitive interface and built-in formula guides.

1. Open your workbook: Launch WPS Office and open the spreadsheet containing your source and destination sheets.
2. Insert the function: Click the cell where you want the data to appear, navigate to the 'Formulas' tab, and click 'Insert Function'.
3. Select VLOOKUP or MATCH: Search for VLOOKUP or INDEX, select it, and follow the simple dialog box prompts to input your criteria.
4. Select ranges with your mouse: Click on your second sheet and drag your mouse over the target range. WPS Spreadsheet will automatically format the cross-sheet reference and add absolute locking ($) if you press F4.
5. Drag to fill: Hit OK and double-click the fill handle to populate the formula down the entire column instantly.

100% compatible with Microsoft Excel formulas and cross-sheet referencingBuilt-in intelligent formula suggestions and error-checking toolsHigh performance for processing large datasets without laggingFree and lightweight alternative to heavy spreadsheet programs

microsoft office alternative - wps office

Frequently Asked Questions

Why is my cross-sheet VLOOKUP returning the same value for every row?

This usually happens if you forgot to use absolute references (adding $ signs) for your table array. Without absolute references, the lookup range shifts downward as you copy the formula down, eventually causing it to miss the data and either return incorrect approximate matches or errors.

What does IFERROR do in my lookup formula?

IFERROR intercepts errors generated by formulas. When VLOOKUP or MATCH cannot find the criteria you specified, it typically displays an #N/A error. Wrapping your formula in IFERROR allows you to display a blank cell or a custom message (like "Not Found") instead.

Can I pull data from a completely different workbook file?

Yes. You can reference another workbook by including its file name in square brackets, followed by the sheet name (e.g., '[SalesData.xlsx]Sheet1!$A$1:$C$10'). Note that both workbooks usually need to be open for the formulas to update reliably in real-time.

Why use INDEX and MATCH instead of VLOOKUP?

INDEX and MATCH can look to the left, meaning the return data column does not have to be to the right of your lookup criteria column. It also processes faster on massive datasets and doesn't break if you insert or delete columns in your source data.

Excel & Data Problems