Forum Discussion

Roymore's avatar
Roymore
Copper Contributor
Jan 18, 2024

Attempting to use vlookup to auto populate sheet and returning #N/A on some cells

The formula I have is =VLOOKUP(TRIM($F2), Serial!$A$2:$C$997, 3, FALSE), so matching a serial number of up to 10-12 numbers, in a serial number database to return customer name and job type.

 

It works half the time, and at times if I click on the serial number database and select the serial number I am looking for it may work, sometimes even copying and pasting the serial number fixes it but not all of the time.

 

I have tried Index and Match formula as well and for the serials that didn't work with the Vlookup also didn't get picked up with index and match. Even copying and pasting the lookup value with the table array value to ensure there wasn't anything strange with the formatting.

 

I tried trim, I have tried it without trim, I am not too sure what is going on.

 

Can anybody offer some advice.

  • Roymore 

    Thanks. Although the relevant columns are both formatted as Text, Excel sees some of the values as text and others as numbers (you can check this using the ISTEXT function).

    If you have Microsoft 365 or Office 2021, you can use this formula in B2:

    =XLOOKUP($D2&"", Serial!$A$2:$A$1000&"", Serial!B$2:B$1000, "")

    Fill to the right to C2, then down to the last used row.

    If you have an older version:

    =IFERROR(INDEX(Serial!B$2:B$1000, MATCH($D2&"", Serial!$A$2:$A$1000&"", 0)), "")

    I'll send you the workbook with both approaches by PM.

  • Roymore 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

    • Roymore's avatar
      Roymore
      Copper Contributor
      Thanks Hans.

      I have sent you a PM
      • Roymore 

        Thanks. Although the relevant columns are both formatted as Text, Excel sees some of the values as text and others as numbers (you can check this using the ISTEXT function).

        If you have Microsoft 365 or Office 2021, you can use this formula in B2:

        =XLOOKUP($D2&"", Serial!$A$2:$A$1000&"", Serial!B$2:B$1000, "")

        Fill to the right to C2, then down to the last used row.

        If you have an older version:

        =IFERROR(INDEX(Serial!B$2:B$1000, MATCH($D2&"", Serial!$A$2:$A$1000&"", 0)), "")

        I'll send you the workbook with both approaches by PM.

Resources