Function Problems

How to Fix VLOOKUP and INDEX Errors Across Excel Worksheets

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is attempting to retrieve product details such as names and quantities from a secondary Excel worksheet but is encountering errors when running the formulas.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching and matching product data across two different worksheets using formulas or macros.
Observed behavior: Attempts to use VLOOKUP, INDEX, a search box, and macros result in #NUM! and #VALUE! errors instead of returning the expected data.

Before you start

Verify that your lookup values in both worksheets share the exact same formatting (e.g., both text or both numbers) and that there are no hidden trailing spaces in the product names.

Solution 1Recommended

Correct the Syntax and Layout for VLOOKUP and INDEX

The #VALUE! and #NUM! errors frequently occur when the formula structure is incorrect, such as invalid column index numbers or mismatched array sizes when referencing another sheet.

A #VALUE! error in VLOOKUP often means your column index number is less than 1, or the lookup value exceeds the 255-character limit. A #NUM! error in INDEX usually occurs if you request a row or column number that is larger than the selected data range.

Verify Worksheet References

Check your formula bar to ensure the external sheet name is properly formatted. If the worksheet name contains spaces, it must be enclosed in single quotes, such as ='Product Data'!A2:C100.

Fix VLOOKUP Column Index

Ensure the col_index_num argument in your VLOOKUP formula is a positive number greater than zero and does not exceed the total number of columns in your specified table_array.

Align INDEX and MATCH Array Sizes

When using INDEX and MATCH together, ensure that the array range selected in the INDEX function is large enough to accommodate the row number being returned by the MATCH function.

Data Layout Check: VLOOKUP requires the lookup value to be in the first column of your table array. If your matching product ID is not in the leftmost column, switch to using INDEX and MATCH.

Solution 2

Clean and Standardize Your Product Data

Mismatched data types, such as numbers stored as text, will cause lookup formulas to fail to find a match and return errors.

Use WPS Spreadsheet

Easily Search and Match Data Across Sheets with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, full compatibility with Excel functions like VLOOKUP and XLOOKUP, and built-in error checking tools to help you seamlessly match product data across worksheets without complex errors.

1. Select Destination Cell: Open WPS Spreadsheet and click on the cell where you want the matched product data to appear.
2. Initiate the Formula: Type =VLOOKUP( or =INDEX( to start your formula and select your lookup value.
3. Select the Target Worksheet: Click on the tab of your second worksheet at the bottom of the screen. WPS will automatically insert the correct cross-sheet syntax.
4. Highlight the Data Range: Drag your mouse over the product data array in the second sheet, ensuring you include all necessary columns.
5. Complete and Apply: Finish typing your column index and match type (e.g., FALSE for an exact match), press Enter, and drag the fill handle down to apply it to your entire list.

100% compatible with Microsoft Excel formulas (.xlsx)Built-in error checking to instantly identify #VALUE! and #NUM! causesUser-friendly formula builder for complex cross-sheet data matchingFree and lightweight alternative for seamless data management

microsoft office alternative - wps office

Frequently Asked Questions

What does a #VALUE! error mean in a VLOOKUP formula?

A #VALUE! error usually indicates that the formula contains an incorrect argument type. In VLOOKUP, it frequently occurs if the column index number is less than 1, or if the lookup value exceeds Excel's 255-character limit.

Why am I getting a #NUM! error when indexing product data?

A #NUM! error generally means there is a problem with a numeric value in the formula. For INDEX functions, this happens if the formula references a row or column number that is larger than the specified array size.

How do I correctly reference a different worksheet in an Excel formula?

To reference another worksheet, use the syntax 'SheetName'!Range. For example: =VLOOKUP(A2, 'Product List'!A:D, 2, FALSE). Always use single quotes around the sheet name if it contains spaces.

Can I use XLOOKUP to prevent across-sheet matching errors?

Yes, if you are using a modern version of spreadsheet software, XLOOKUP is often simpler and less prone to errors than VLOOKUP. It does not require a column index number, making it immune to the #REF! and #VALUE! errors caused by inserting or deleting columns.

Excel & Data Problems