Share via

excel (xlsx) vlookup

T Bars 0 Reputation points
2026-05-24T16:01:32.7566667+00:00

I am a beginner with vlookup. I am trying to combine sales information and stock on hand for 3 stores that have like items in different excel sheets using Vlookup. I have items in all three sheets that are exact matches and some unique to each file. Some come back with my correct inventory counts for sold and some exact matches do not. I get a N/A result. as shown above the Wolf Grey show in two stores on hand and sold but some sizes show N/A I know were sold and some are in stock from the reports run but I can't get them all to show

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EmilyS726 236K Reputation points Independent Advisor
    2026-05-24T17:19:07.52+00:00

    It is difficult to troubleshoot this accurately without seeing the actual VLOOKUP formula and the layout of the data being referenced. VLOOKUP is very sensitive to the lookup value, the selected table range, the column number being returned, and whether the match type is set correctly.

    Even when two item names look identical, Excel may still treat them as different if there are hidden spaces, different characters, inconsistent size formatting, or if the lookup range does not include the correct row or column. That can cause #N/A even when the item appears to exist in both sheets.

    Please share the VLOOKUP formula you are using, along with a sample of how the data is arranged in each sheet, including the relevant columns and rows. A screenshot with row and column headers visible would also help.

    Was this answer helpful?

    0 comments

  2. AI answer

    2026-05-24T16:01:47.0933333+00:00

    #N/A with VLOOKUP almost always means the lookup formula cannot find an exact match for the lookup value in the lookup range, even if the item “looks” the same on the sheet.

    For combining sales and stock across store sheets, check these points for the rows that return #N/A while others work:

    1. Use exact match in VLOOKUP
      Make sure the last argument in VLOOKUP is FALSE (or 0) so Excel looks for an exact match: 
         =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
      If it is TRUE or omitted, VLOOKUP does an approximate match and can return #N/A or wrong values, especially if the first column is not sorted.
    2. Confirm the lookup value actually exists in the lookup table
      For a size that returns #N/A:
      • Go to the sheet that is used as table_array.
      • Look in the first column of that range and verify that the exact item/size combination is present.
      • If the item/size is missing in that store’s sheet, VLOOKUP will correctly return #N/A.
    3. Check that the lookup value is in the first column of the table_array
      VLOOKUP only searches the left‑most column of the range you pass as table_array.
      • If the item code or item+size is not in the first column of that range, VLOOKUP will not find it.
      • Adjust the range so that the item identifier column is the first column in table_array.
    4. Check for data type mismatches (numbers vs text)
      Sometimes the same-looking value is stored as text in one sheet and as a number in another, or has hidden spaces.
      • Select a cell with the item/size in the source sheet and a matching one in the lookup sheet.
      • Use Format Cells → Number to confirm they are the same type (both Text or both Number).
      • If needed, reformat the entire column, then use Data → Text to Columns → Finish to force Excel to reapply the format.
    5. Handle unavoidable #N/A with IFERROR
      If some items really do not exist in a particular store’s sheet, wrap the VLOOKUP in IFERROR to avoid showing #N/A: 
         =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, FALSE), "")
      Replace "" with a message like "Not found" if preferred.

    If after these checks some sizes still show #N/A, focus on one failing example and compare it character‑by‑character (and format‑by‑format) with a working one in the lookup range; almost always there is a subtle difference in value or formatting.

    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.