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

https://docs.google.com/spreadsheets/d/1LNiRPoi-3ZtViIL8JhJ_Qd2jo3XUEpfMLwlmlOz1jTs/edit#gid=895445914

VLOOKUP Formula Being Used

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

Error Received

Error
Did not find value '100103' in VLOOKUP evaluation.

Google Product Forum Post

https://productforums.google.com/forum/#!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+"))))