Google Sheet Vlookup is not working right with nested function

1
votes

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.

1
google-sheetsstring-formattingnumber-formattinggoogle-sheets-formulags-vlookup

1 Answers

0
votes

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"), ))