How to Replace Scanned IDs with Names Using XLOOKUP in Excel
Question details
The user wants to automatically convert or display employee names in Excel when their badge ID is entered via a scanner.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using a barcode or badge scanner to input employee ID numbers into an Excel spreadsheet for tracking or attendance.
- Observed behavior
- Currently, only the scanned ID is recorded, and the goal is to automatically display the corresponding employee's name using a lookup function.
Ensure you have a master table within your Excel workbook that pairs every employee ID with their correct name or nickname.
Use XLOOKUP to Match Scanned IDs to Employee Names
Create an XLOOKUP formula to dynamically pull the employee's name from a master list whenever an ID is scanned.
The XLOOKUP function is the most efficient way to search for an ID in a column and return the corresponding name from another column. It handles missing data gracefully and does not require the lookup array to be sorted.
In a separate sheet (e.g., Sheet2), create a master list with Employee IDs in Column A and their corresponding Names in Column B.
In your tracking sheet where the scanner inputs IDs (e.g., Column A), select the adjacent cell in Column B where you want the name to appear.
Type the formula: =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not Found"). Here, A2 is the cell where the ID is scanned, Sheet2!A:A is the ID range, and Sheet2!B:B is the name range.
Press Enter to confirm the formula, then drag the fill handle down to apply it to all rows where badge IDs will be scanned.
Easily Match Scanned Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, allowing you to instantly match scanned badge IDs to employee names. It is a highly compatible and lightweight alternative for your data tracking needs.
- 1. Open your tracking file: Launch WPS Spreadsheet and open your employee database or attendance tracking file.
- 2. Select the output cell: Click on the cell where you want the scanned employee's name to be displayed.
- 3. Input the XLOOKUP formula: Type =XLOOKUP( and select the scanned ID cell, followed by your ID reference column, and your Name reference column.
- 4. Execute and drag: Press Enter to execute the formula and drag it down for continuous scanning tracking.
Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP for scanned IDs?
Yes, if your version of Excel does not support XLOOKUP, you can use VLOOKUP. The formula would look like =VLOOKUP(A2, Sheet2!A:B, 2, FALSE). However, VLOOKUP requires the ID column to be on the left side of the Name column.
Why is my XLOOKUP returning an #N/A error?
An #N/A error means the scanned ID was not found in your lookup array. Ensure there are no leading or trailing spaces in your ID numbers, and verify that the scanned ID matches the exact format (text vs. number) of the master list.
How do I make the scanner automatically move to the next row?
Most barcode and badge scanners can be configured to automatically send an 'Enter' or 'Tab' keystroke after each scan. Check your scanner's manual to scan the configuration barcode that enables the 'Carriage Return' suffix.
Can XLOOKUP return both the first and last name if they are in separate columns?
Yes, you can use the ampersand (&) operator to concatenate columns in the return array. Format your return array as Sheet2!B:B & " " & Sheet2!C:C inside the XLOOKUP function to combine the first and last names.
