Function Problems

How to Use VLOOKUP to Match Transponder Numbers to License Plates in Excel

Muhammad Talha Oct 12, 2026 871 views

Question details

The user needs to retrieve vehicle license plates based on transponder numbers located on a different worksheet to assign toll expenses accurately to the correct user group.

How to Use VLOOKUP to Match Transponder Numbers to License Plates

Product: Excel
Device & OS: not provided
Scenario: Matching transaction data to a reference table located on another worksheet using a lookup function.
Observed behavior: Needs to correctly pull license plate strings into the transaction worksheet based on exact transponder number matches.

Before you start

Ensure both the transaction worksheet and the mapping worksheet are open in your workbook, and verify that the transponder numbers in both sheets are formatted identically (e.g., both as text or both as numbers) to avoid match failures.

Solution 1Recommended

Use VLOOKUP for an Exact Match Across Worksheets

Apply the VLOOKUP function with absolute references to pull data from a separate mapping sheet into your main transaction list.

When working with toll expenses and vehicle groups, using FALSE in your VLOOKUP formula ensures that you only get exact matches for your transponder numbers. Applying absolute references (using the $ sign) prevents the lookup range from shifting out of place when you copy the formula down your dataset.

Add a new column for license plates

In your main transaction worksheet, insert a new column next to your transaction transponder numbers to hold the retrieved license plate data.

Enter the VLOOKUP formula

Select the first empty cell in your new column (for example, C2). Type the formula =VLOOKUP(B2,Sheet2!$A$2:$B$61,2,FALSE). In this formula, B2 is your transponder number, Sheet2!$A$2:$B$61 is your mapping table on the second sheet, 2 is the column containing the license plates, and FALSE enforces an exact match.

Copy the formula down the column

Press Enter to apply the formula. Then, double-click the fill handle (the small square at the bottom-right corner of the selected cell) to automatically copy the VLOOKUP formula down to all remaining rows in your transaction list.

Use VLOOKUP for an Exact Match Across Worksheets

Handle Missing Matches Gracefully: Wrap your VLOOKUP in an IFERROR function, such as =IFERROR(VLOOKUP(B2,Sheet2!$A$2:$B$61,2,FALSE), "Unregistered Vehicle"), to display a custom text message instead of an #N/A error for unmatched transponders.

Advanced Data Analysis

Use VLOOKUP Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports VLOOKUP and other advanced data analysis functions, allowing you to seamlessly match cross-worksheet data like transponder numbers and license plates with helpful formula prompts.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your transaction and transponder mapping worksheets.
2. Start the VLOOKUP function: Select the target cell for the license plate and type =VLOOKUP(. Click on the cell containing the transaction's transponder number to set your lookup value.
3. Select the reference array: Navigate to your reference worksheet, highlight the data range containing both transponder numbers and license plates, and press F4 on your keyboard to lock the reference into an absolute range.
4. Complete and apply the formula: Type ,2,FALSE) to specify the license plate column index and require an exact match. Press Enter, then double-click the cell's fill handle to apply it to all rows.

Fully compatible with Microsoft Excel formulas, functions, and cross-worksheet referencingIntuitive formula-building interface with built-in syntax hintsHandles large datasets smoothly without lagging or crashingFree and lightweight alternative for professional data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

This usually happens when the exact transponder number is not found in the reference table. Check for extra spaces (using the TRIM function), verify that data formats match (e.g., both are formatted as Text or both as Numbers), and ensure you used FALSE as the final argument for an exact match.

What does the $ symbol do in the VLOOKUP reference range?

The $ symbol creates an absolute reference (such as $A$2:$B$61). This locks the reference table's coordinates so the range does not shift downward when you copy the formula into the rows below it.

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, if your spreadsheet software version supports it. XLOOKUP is a modern, more flexible function that defaults to an exact match and can look up data in any direction. The equivalent formula would be =XLOOKUP(B2, Sheet2!$A$2:$A$61, Sheet2!$B$2:$B$61).

How do I reference a mapping table in a completely different workbook?

Keep both workbooks open. Start typing your VLOOKUP formula, and when it is time to select the table array, click over to the window of the other workbook and highlight the range. The application will automatically insert the external workbook's file path into your formula.

Excel & Data Problems