Forum Discussion

rodsan724's avatar
rodsan724
Brass Contributor
Oct 16, 2021
Solved

Why isn't my vlookup returning most of the values?

After I refresh my database Query 1 table, I try to lookup the slic column in Table2 to get the description in Table3. However, Only one of the descriptions is coming back. Any ideas? To be more exac...
Formulas and Functions
  • rodsan724's avatar
    rodsan724
    Oct 16, 2021
    Figured it out!

    =VLOOKUP(IF(ISNUMBER(VALUE(D2)),NUMBERVALUE(D2),D2),Table3,2,FALSE)

    VLOOKUP
    ISNUMBER
    VALUE
    NUMBERVALUE
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 16, 2021

rodsan724 Most likely the 10, 20 and 30 are not the same type in both tables. One could be text and the other a number. Or if they are text in both tables, one could have one or more trailing spaces. Hence, #N/A as VLOOKUP doesn't find an exact match.

rodsan724's avatar
rodsan724
Brass Contributor
Oct 16, 2021

I attached the file to OP. I wasn't sure if I should l leave the data source so I deleted it. I can put back in if you like, I wasn't sure if the values would come in without the data source. I have control over Table3 but not the data source.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 16, 2021

    rodsan724 Can't really tell where it went wrong, but some the data types were not in sync. I made it all Text and redid the VLOOKUP. Now it seems to work as expected. See attached.

     

     

     

    • rodsan724's avatar
      rodsan724
      Brass Contributor
      Oct 16, 2021
      The only control I have is over Table 3. What do I have to do to fix it? If I double click the Id cell and press enter it seems to fix itself. However, in my production case, I have thousands of records like this and can't manually do it for all those rows.

Resources