Excel 365 vlookup does not work. Piece of junk.

Lynn Kerber 0 Reputation points
2026-03-31T16:53:01.7866667+00:00

Excel 365 vlookup function does not work when array name is another excel365 file.

This product is falsely advertised as Excel

The product is a piece of JUNK.

Microsoft 365 and Office | Excel | Other | Other
0 comments

1 answer

Sort by: Newest
Most helpful Newest Oldest

  1. AI answer

    2026-03-31T16:53:27.3333333+00:00

    VLOOKUP in Excel 365 works with ranges in other workbooks, but several common issues can make it appear broken.

    Key points to check when the table_array is in another Excel file:

    1. Confirm the basic VLOOKUP pattern Use the standard syntax: 
         =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
      When the table_array is in another workbook, Excel will include the full path, workbook, and sheet name automatically once that range is selected.
    2. Check for common VLOOKUP errors The behavior depends on what error appears:
      • #N/A: The lookup value is not found in the first column of the external table, or formatting does not match. For exact matches, set the last argument to FALSE.
      • #REF!: The col_index_num is larger than the number of columns in the external table_array.
      • #VALUE!:
        • If table_array is less than 1, VLOOKUP returns #VALUE!.
        • If lookup_value is more than 255 characters, VLOOKUP can also fail with #VALUE!. In that case, use INDEX + MATCH instead (see below).
    3. If lookup_value is longer than 255 characters VLOOKUP has a known limitation when lookup_value exceeds 255 characters. When that happens, use INDEX and MATCH instead of VLOOKUP, even if the data is in another workbook. Example pattern (works across workbooks as well): 
         =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
      If used as an array formula in non–dynamic-array Excel, confirm with CTRL+SHIFT+ENTER instead of just ENTER, as described in the documentation. In current Microsoft 365 versions, ENTER is sufficient.
    4. Consider using XLOOKUP instead of VLOOKUP XLOOKUP is the recommended replacement for VLOOKUP and works more flexibly with external ranges, returns exact matches by default, and avoids several VLOOKUP limitations.

    If the formula still fails, check:

    • The external workbook is accessible and not corrupted.
    • The first column of the external table_array actually contains the lookup values.
    • The column index is within the bounds of the external table_array.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.