Function Problems

How to Use Excel VLOOKUP to Return Commission from Another Worksheet

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to retrieve a commission rate from an 'Experiences' worksheet and display it on an 'Orders' worksheet based on a selected experience.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating sales or order commissions by dynamically pulling data from a separate reference sheet.
Observed behavior: The user wants to automate commission retrieval across different worksheets using a lookup function, avoiding manual data entry and cross-referencing errors.

Before you start

Ensure that the lookup values in your destination sheet and the source sheet match exactly; hidden spaces or text formatting differences will cause the VLOOKUP formula to fail.

Solution 1Recommended

Use VLOOKUP Combined with IFERROR

Use the standard VLOOKUP function wrapped in an IFERROR function to reliably match data across different worksheets and handle unfound items cleanly.

VLOOKUP is designed to search for a specific value in the first column of a table array and return a value in the same row from another column. When working across worksheets, you simply add the sheet name followed by an exclamation mark before the cell range.

Select the target cell

Open your workbook and click on the cell in the 'Orders' sheet where you want the commission to appear (for example, cell H7).

Enter the VLOOKUP formula

Type the formula: =IFERROR(VLOOKUP(D7, 'Experiences'!B:I, 7, FALSE), "Not Found"). In this formula, D7 is the experience selected on the Orders sheet, 'Experiences'!B:I is the data range on the source sheet, 7 is the column index containing the commission, and FALSE requires an exact match.

Copy the formula down

Press Enter to calculate the result. Then, click the small square at the bottom-right corner of the cell (the fill handle) and drag it down the column to apply the formula to the rest of your data.

Adjusting Formula References: Make sure to adjust the column index number (7 in the example) to match the exact column in your 'Experiences' sheet. It is highly recommended to use absolute references (e.g., 'Experiences'!$B$2:$I$100) instead of entire columns (B:I) for better spreadsheet performance.

Solution 2

Use XLOOKUP for Enhanced Flexibility

If you are using a newer version of Excel or WPS Office, XLOOKUP provides a more robust alternative that does not require counting column indexes.

Work with Data Seamlessly

Easily Cross-Reference Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like VLOOKUP, XLOOKUP, and IFERROR, allowing you to easily pull commission rates from different worksheets. It provides powerful formula handling capabilities identical to Microsoft Excel.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the workbook containing your 'Orders' and 'Experiences' sheets.
2. Insert the Lookup formula: Select the target commission cell, type =VLOOKUP(, and use your mouse to highlight the reference ranges on the other worksheet.
3. Drag to fill: Hit Enter, then use the fill handle on the cell's bottom-right corner to drag the formula down the entire column.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulasSupports advanced lookup functions including VLOOKUP, HLOOKUP, and XLOOKUPLightweight, fast performance even with large datasets containing complex formulasFree to download and use with a highly intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error when referencing another sheet?

An #N/A error usually means the lookup value does not exist in the source range, or there are formatting differences such as trailing spaces or numbers stored as text. Ensure exact matches and always use the FALSE argument in your VLOOKUP formula for exact matching.

How do I lock the table array reference when dragging the formula down?

You need to make the table array an absolute reference by selecting the range in the formula bar and pressing F4. This adds dollar signs (e.g., 'Experiences'!$B$1:$I$100) and prevents the search range from shifting downwards as you drag the formula down the column.

Can I use VLOOKUP to pull data from a completely different workbook file?

Yes, you can reference another workbook by keeping it open and clicking into it while writing the VLOOKUP formula. The spreadsheet application will automatically insert the file path, workbook name, and sheet name into the formula.

Excel & Data Problems