How to Use VLOOKUP in WPS Office Excel to Return a Name from a Phone Number
Question details
The user needs to match phone numbers in one WPS Office Excel worksheet with a phone-number list in another worksheet and return the corresponding name using a lookup formula.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A phone number on one worksheet must be matched against a phone-number list on another worksheet so the associated name can be returned.
- Observed behavior
- The user wants the correct name to appear beside the phone number after entering a lookup formula.
Make sure both worksheets are in the same workbook or in a referenced workbook you can open, and confirm the phone numbers are stored in a consistent format, for example all as text or all as numbers with no extra spaces, so the lookup can match exactly.
Use VLOOKUP to Return the Matching Name from Another Worksheet
Use this exact-match VLOOKUP formula when your phone-number list is in a separate worksheet and the phone number is in the first column of the selected range.
VLOOKUP searches the first column of the range you select and returns a value from a column to its right. The FALSE argument forces an exact match, which is important because phone numbers often need character-for-character equality.
Open your workbook in WPS Office and click the cell where you want the matching name to appear, such as B2 beside the phone number in A2.
Type =VLOOKUP(A2,Sheet1!$B$2:$H$3,7,FALSE) and press Enter. This searches for the phone number in A2 within the first column of Sheet1!$B$2:$H$3 and returns the value from the seventh column.
If the result is correct, drag the fill handle from the formula cell down through the remaining phone-number rows. Keep the lookup range absolute with $ signs so it does not shift.
Use XLOOKUP as a Flexible Alternative for Newer WPS Office Versions
Use this approach if your version of WPS Office supports XLOOKUP and you do not want to count the return column position manually.
Match Phone Numbers to Names in WPS Office Excel
WPS Office Excel supports the same VLOOKUP and XLOOKUP lookup workflows as Microsoft Excel. You can open your existing workbook, enter an exact-match formula, and return the corresponding name without changing your file format.
- 1. Open your workbook in WPS Office: Launch WPS Office, click Open, and select the workbook that contains the phone-number sheet and the name list sheet.
- 2. Select the result cell: Go to the worksheet where you need the name and click the cell beside the first phone number, such as B2.
- 3. Enter the lookup formula: Type =VLOOKUP(A2,Sheet1!$B$2:$H$3,7,FALSE) or the XLOOKUP alternative, then press Enter to return the matching name.
- 4. Apply the formula to other rows: Drag the fill handle down to copy the formula to the remaining phone numbers.
- 5. Save in Excel-compatible format: Click Menu and save the workbook as .xlsx or .xls to keep the formulas and results available in Microsoft Excel.
Frequently Asked Questions
Why does VLOOKUP return #N/A even though the phone number is visible?
In WPS Office Excel, #N/A usually means VLOOKUP could not find an exact match. Check for extra spaces with TRIM, check apostrophes or text formatting, and confirm the lookup range starts with the phone-number column. Use FALSE for exact match.
Can I use VLOOKUP if phone numbers are stored as text in one sheet and as numbers in another?
VLOOKUP sees text and number phone numbers as different. Convert both sides to the same format, for example by using Text to Columns or wrapping the lookup value with TEXT(A2,"0"), before applying the formula.
How do I know which column number to use in VLOOKUP?
Count the columns from the first column in your selected lookup range to the column containing the names. For example, if the range is Sheet1!$B$2:$H$3, B is column 1 and H is column 7.
Does WPS Office support XLOOKUP for returning a name from a phone number?
Newer WPS Office versions support XLOOKUP. You can enter =XLOOKUP(A2,Sheet1!$B$2:$B$3,Sheet1!$H$2:$H$3,"Not found") to return the matching name flexibly.
How do I copy the VLOOKUP formula without changing the phone-number list range?
Use absolute references for the lookup range, such as Sheet1!$B$2:$H$3, then drag the fill handle down. The lookup range stays fixed while the lookup cell reference updates.
