Search code examples
google-sheetsstring-formattingnumber-formattinggoogle-sheets-formulags-vlookup

Google Sheet Vlookup is not working right with nested function

I'm having trouble trying to run a VLOOKUP query in Google Sheets. I'm trying to see if a value already exists in a given column. However, I need to sanitize the inputs since the provided numbers have 9 digits, and the inputs have 12. For example,

  • Cell A1 - Given Value - 123456789
  • Cell B1 - Inputs --------- 999123456789

I get the needed value from the input using the RIGHT Function taking the last 9 values

  • Cell C1 - =RIGHT(B1,9)

Then run the VLOOKUP function

  • Cell D1 - =VLOOKUP(C1,B:B,1,0)

The result in get in Cell D1 is:

N/A. The error I get is "Did not find 123456789 in the VLOOKUP evaluation"

VLOOKUP Error

I'm not sure what I'm doing wrong here since this formula works correctly in Excel.

Solution

  • the issue is that RIGHT converts number to text string

    the solution is:

    =VLOOKUP(C1*1, A1, 1, 0)

    and here you can see what's going on:

    enter image description here

    or directly:

    =VLOOKUP(RIGHT(B1, 9)*1, A1, 1, 0)

    but if you just want to check if partial number is present in full number you can do:

    =REGEXEXTRACT(B1&"", A1&"")

    and ArrayFormula of that would be:

    =ARRAYFORMULA(IF(A1:A<>"", IFERROR(REGEXEXTRACT(B1:B&"", A1&""), "no"), ))