How to Look Up a Value on Another Sheet and Return Its ID
Question details
The user needs to match a concatenated value on one sheet against another sheet's column and return the corresponding ID, optionally appending it to the matched text.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up specific values across multiple sheets to merge records or retrieve related IDs based on matching criteria.
- Observed behavior
- Needs a formula to correctly return the associated ID from Sheet 1 based on the matched concatenated values from Sheet 2.
Ensure your data is organized into structured Excel tables (e.g., Table1) for easier referencing, and verify that the lookup columns contain exact matching text strings to avoid #N/A errors.
Use XLOOKUP to Find and Append the ID
XLOOKUP is the most robust and flexible function for finding values across sheets, allowing you to easily handle missing data and append text dynamically.
The XLOOKUP function provides a simplified way to search for a value in one column and return a value from another. By combining it with the ampersand (&) operator, you can simultaneously pull the ID and append it to your original text string.
Click on the cell in your destination sheet (S2) where you want the combined ID and text to appear.
To return just the ID, type the formula: =XLOOKUP([@CONCAT1],Table1[CONCAT2],Table1[ID],""). This searches for the CONCAT1 value within Table1's CONCAT2 column.
To add the matched text to your result, append it using the ampersand operator: =XLOOKUP([@CONCAT1],Table1[CONCAT2],Table1[ID],"")&[@CONCAT1].
Press Enter to calculate the result, then drag the fill handle down to apply this formula to the rest of the cells in your column.
Use VLOOKUP for Older Versions
If you are using an older spreadsheet version that does not support the XLOOKUP function, you can achieve similar results using the traditional VLOOKUP formula.
Easily Use XLOOKUP and Advanced Functions in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP and VLOOKUP natively, making cross-sheet data lookups simple, efficient, and perfectly compatible with your existing files.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your workbook containing the data tables.
- 2. Access the Formulas Tab: Navigate to the Formulas tab and click 'Insert Function' to easily search for and use the XLOOKUP function.
- 3. Input Arguments: Use the intuitive Function Arguments dialog box to input your lookup value, lookup array, and return array without manually typing the syntax.
Frequently Asked Questions
Why is my VLOOKUP returning an #N/A error?
This usually happens if the lookup value doesn't exist in the target range, or if there are trailing spaces in your text. Make sure both columns have exact matches and consider using the TRIM function to remove hidden spaces.
Can I use XLOOKUP across different workbooks?
Yes, you can reference ranges in completely different, open workbooks. Simply select the range in the other file while writing your XLOOKUP formula, and the application will create the external link automatically.
How do I return only the ID without appending the text?
Simply use the base XLOOKUP formula without the ampersand (&) operator at the end. Use: =XLOOKUP([@CONCAT1],Table1[CONCAT2],Table1[ID],"").
What does the empty string quote ("") do in the XLOOKUP formula?
The empty string ("") in the fourth argument tells XLOOKUP to return a blank cell instead of an #N/A error if no match is found, keeping your data sheet clean.
