How to Look Up an Amount by Number and Return the Matching ID in Excel
Question details
The user needs a formula to look up a number in column C and return the corresponding amount from column E or the matching ID from column A.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a worksheet where column C contains lookup numbers, column E contains amounts, and column A contains IDs.
- Observed behavior
- The desired outcome is to enter a lookup number and have WPS Office return the corresponding amount or ID.
Confirm that the lookup numbers are in column C, amounts are in column E, and IDs are in column A. If the workbook uses a formatted Excel table, replace full-column references with the equivalent table references.
Use XLOOKUP to Return the Matching Amount or ID
Use this method in modern Excel and supported WPS Office versions when you want a straightforward formula with a built-in not-found message.
XLOOKUP searches column C for the lookup value in G1 and returns the value from the same row in either column E or column A.
Click the cell where you want the amount to appear, such as G2 when the lookup number is in G1.
Type =XLOOKUP(G1,C:C,E:E,"Not found") and press Enter to return the matching amount.
Click another result cell, type =XLOOKUP(G1,C:C,A:A,"Not found") and press Enter to return the matching ID.
Use INDEX and MATCH for Broad Compatibility
Use this method if you are working with older Excel files or a WPS Office version where XLOOKUP is not available.
Use FILTER to Return Every Matching Row
Use this method when the lookup number may appear more than once and you need all matching rows.
Return the Matching Amount or ID with WPS Office Spreadsheets
Open the same workbook in WPS Office and use XLOOKUP, INDEX and MATCH, or FILTER to look up a number in column C and return the matching amount from column E or ID from column A. The formulas work the same way as in Excel, so you can keep your existing file and data structure.
- 1. Open the workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx file that contains columns A, C, and E.
- 2. Click the result cell: Select the cell where you want to display the matching amount or ID.
- 3. Enter the lookup formula: Type =XLOOKUP(G1,C:C,E:E,"Not found") for the amount, or =XLOOKUP(G1,C:C,A:A,"Not found") for the ID, and press Enter. If XLOOKUP is not available in your version, use =INDEX(E:E,MATCH(G1,C:C,0)) or =INDEX(A:A,MATCH(G1,C:C,0)).
- 4. Verify the returned value: Change the lookup number in G1 and confirm that the formula updates to the correct amount or ID.
Frequently Asked Questions
Which formula should I use if my version of Excel does not have XLOOKUP?
Use INDEX and MATCH. Click the result cell and enter =INDEX(E:E,MATCH(G1,C:C,0)) for the amount, or =INDEX(A:A,MATCH(G1,C:C,0)) for the ID, then press Enter.
Can I return both the ID and the amount with one formula?
A single formula normally returns one value. Put two XLOOKUP or INDEX/MATCH formulas in separate cells, or use =FILTER(A:E,C:C=G1,"Not found") to return the entire matching row range.
What happens if the lookup number is not in column C?
XLOOKUP returns the custom message you provide, such as "Not found". INDEX and MATCH returns #N/A unless you wrap it in IFERROR, for example =IFERROR(INDEX(E:E,MATCH(G1,C:C,0)),"Not found").
Can these lookup formulas be used in WPS Office?
Yes. WPS Office Spreadsheets supports these formulas in supported versions and can open .xlsx files while keeping your Excel formulas intact. If XLOOKUP is not available in an older WPS build, use the INDEX and MATCH formula instead.
Why does my INDEX and MATCH formula return #N/A even when the number seems to be present?
This usually means the lookup value in G1 and the values in column C have different data types, such as one being text and the other a number. Check the cell formats, or use TRIM or VALUE to normalize the values.
