Google-sheets – VLOOKUP problem with value from =arrayformula(iferror(regexextract

formulasgoogle sheetsregexextractvlookup

I'm trying to use VLOOKUP to pull data from one tab in a Google Sheets workbook into another tab. The VLOOKUP formula works if the value being searched for is 100103, but not the cell reference G2 (which displays 100103).

Formula In G2

=arrayformula(iferror(regexextract(regexextract(F2:F, "ID#\d+"), "\d+"))) 

Shared Copy Of Original Google Sheet

VLOOKUP Formula Being Used

=VLOOKUP($G2,workshops!$E:$K,2, FALSE)

Error Received

Did not find value '100103' in VLOOKUP evaluation.

Google Product Forum Post!topic/docs/MLv28H7msmw

The user is experiencing a similar problem. They received a solution, but not an explanation of the solution. As a result, I'm not sure how to adjust my formula(s).

Any ideas how to solve this?

Best Answer

There is a difference between the string of characters "100103" and the number 100103. Your workshops tab contains numbers. regexextract returns a string. To make things work, convert string to number using value after regexextract:

=arrayformula(iferror(value(regexextract(regexextract(F2:F, "ID#\d+"), "\d+"))))