'The same vlookup/match works by itself but broken with arrayformula (for certain type of the lookup value)
The first spreadsheet "Book" contains input of range and row I want to fetch data from the second sheet "Bell". There is one type of the lookup values which doesn't show anything in the arrayformula.
I tried the individual functions of both vlookup and match, they worked fine. But once I put them into the arrayformula, it said
Did not find value '......' in VLOOKUP/MATCH evaluation.
Here are sample of the code as standalone
=vlookup(F1,indirect("Bell!"&J1,TRUE),2,FALSE)
And here is when I place it into an arrayformula
=arrayformula(vlookup(F2:F,indirect("Bell!"&J2:J,TRUE),2,FALSE))
The column J is the A1 notation of the range I want the vlookup to search.
The only problem is there is one type of value that cannot be evaluated, specifically it is either "2-10/12" or "2-11". I suspected that maybe it is because the these 2 values are not number. (The other possible lookup values are number 1 to 8.).
How fix there issue? I tried to format the lookup value Book!F:F and all the possible columns like Bell!J:J as "Plain text", it doesn't work. I tried TO_TEXT, also doesn't work.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|