VLookup not working on full numbered Value

Question

I created a Google Spreadsheet (File Name Product Test) and I have an ID field on column A which contains the word ID plus some letters and numbers (Example "ID-KNYT-12345"). The KNYT part is different per ID, some KNYT some DMXF etc.

So in column B I added a custom formula (Sample below) which processes the ID. If KNYT only numbers are kept. If DMXF the DMXF part is included plus the numbers.

I then have a vlookup/importrange formula on column C which is supposed to use the converted value in column B to lookup the value from another sheet and retrieve a certain information.

The problem is if the converted value contains all numbers like 12345 the vlookup fails, "Did not find value in lookup evaluation". If the converted value contains letters and numbers like DMXF-25452 the lookup works. If I manually type 12345 on column A the lookup works. Why would the lookup say it didn't find a result when the value is there? More details below

• I checked, all cells involved are in format Number>"AUTOMATIC".
• I checked, the value 12345 is definitely found on the other sheet (Imported Range)
• I checked these values online, I found no hidden characters or spaces
• The data is from an email with attached Excel file. I don't download the file, I just click to preview it and copy-paste the entire table over to my Product Test spreadsheet

The custom Formula:

function Convert(Thevalue) 
{
  Thevalue = Thevalue.toString().replace("ID-KNYT-", "");
  Thevalue = Thevalue.toString().replace("ID-DMXF-", "DMXF-");

  if (Thevalue == "DMXF-2245"){Thevalue = "Evaluated";}
  if (Thevalue == "DMXF-3268"){Thevalue = "Pending";}

  return Thevalue;
}

The Vlookup (Not actual sheet url just a sample)

VLOOKUP($B1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/feiugsdfjhsdkjfhiesdfjh-p-dsflkjgsdf/edit#gid=000222333","sheet1!$A:$C"),3,FALSE)

UPDATE: This seem to fix it for me. Looks like if the return value is all numbers and no letters it is a NaN issue

  if (!isNaN(Thevalue))
  {
    return Number(Thevalue);
  }
  else
  {
    return Thevalue;
  }

Answer 1

Your custom function returns text strings. The vlookup() function does not consider the number 123 and the text string "123" equal. To make it work, convert the lookup keys to text with to_text(), like this:

=vlookup( to_text($B1), Sheet1!$A:$C, columns(Sheet1!$A:$C), false )

As RemcoE33 said, the custom function seems superfluous, because the same thing can be done with a plain vanilla spreadsheet formula that employs regexreplace(), like this:

=arrayformula( regexreplace( to_text(A2:A), "ID(?:-KNYT)?-", "" ) )