How to Pull Matching Data from Another Excel Sheet by Loan Number
Question details
Retrieve an invoice or investment value from a secondary report into a master sheet when the loan numbers match.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating financial data across multiple reports based on a unique identifier, such as a loan number.
- Observed behavior
- Requires an efficient formula to cross-reference loan numbers between two sheets and extract the corresponding data values into the correct rows.
Verify that both your master sheet and the secondary report are open, and ensure the 'Loan Number' columns in both sheets share the exact same data format (e.g., both are formatted as Text or both as Numbers) to avoid formula mismatch errors.
Use the XLOOKUP Function (Recommended)
XLOOKUP is the most efficient and modern function to find a matching loan number and return corresponding values without worrying about the order of your columns.
The XLOOKUP function replaces older functions like VLOOKUP. It allows you to look up a value in one column and return a corresponding value from another column, even if the return column is to the left of your lookup array.
Click on the first empty cell in your master sheet where you want the retrieved value (e.g., the invoice amount) to appear.
Type `=XLOOKUP(A2, 'Sheet 2'!B:B, 'Sheet 2'!A:A, "")` into the formula bar. In this example, 'A2' represents the cell containing the loan number in your current sheet.
Replace 'Sheet 2'!B:B with the actual column containing the loan numbers in your secondary report, and 'Sheet 2'!A:A with the column containing the data you want to pull back.
Press Enter to view the result. Then, click the small square fill handle at the bottom-right corner of the cell and drag it down to apply the formula to the remaining rows.
Use Power Query for Regularly Updated Reports
If you frequently receive updated loan reports and need to match data continually, merging queries is more reliable and automated than using formulas.
Use WPS Spreadsheet to Easily Match and Pull Financial Data
WPS Spreadsheet provides robust support for advanced lookup functions like XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to seamlessly pull data across multiple sheets and large financial workbooks.
- 1. Open your files in WPS: Launch WPS Spreadsheet and open the workbook containing your master and secondary loan reports.
- 2. Access the function library: Click on the cell where you want the matching data. Go to the Formulas tab on the ribbon and click 'Insert Function'.
- 3. Use XLOOKUP: Search for XLOOKUP, then fill out the Lookup_value, Lookup_array, and Return_array fields in the pop-up dialog box to accurately pull your data.
Frequently Asked Questions
Why is my XLOOKUP formula returning an #N/A error?
An #N/A error occurs when the exact loan number from your first sheet cannot be found in the designated column of your second sheet. This can be caused by the number genuinely missing, or by a formatting mismatch (e.g., one is formatted as text and the other as a number). Adding `""` to the fourth argument of your XLOOKUP formula will display a blank cell instead of the error.
Can I pull data from a completely different spreadsheet file?
Yes. You can reference external workbooks. Ensure both files are open, type `=XLOOKUP(`, click on your lookup value, and then switch to the other workbook to select your arrays. The formula will automatically capture the external file path.
Is VLOOKUP still an option for this task?
Yes, VLOOKUP works well, provided that the 'Loan Number' column is positioned to the left of the value you want to retrieve in your secondary sheet. However, XLOOKUP is generally preferred because it does not require a specific column order and is less prone to breaking if new columns are inserted.
