I know there have been many questions regarding this and I did read through these (+ the answers). But the formula suggested in each of them does not seem to work for me.
I have created a sheet for other people to look at, so that you all understand what I have been trying to do. I basically want my VLookup to search for the first 5 characters, but it's giving me an error. I used the formula that was suggested here in some other threads, but it doesn't seem to give any results.
Any help is much appreciated.
Use left(), like this:
=arrayformula( if( len(A2:A); iferror( vlookup( left(A2:A; 5) & "*"; F2:G; columns(F2:G); false) ); iferror(1/0) ) )
This array formula will automatically fill the whole column.
To do the same with a fill-down formula, remove the arrayformula() wrapper and use a row absolute reference, like this:
=vlookup( left(A2; 5) & "*"; F$2:G; columns(F$2:G); false)
See your sample spreadsheet for an illustration.