excel - Vlookup macro doesn't return all values needed but the values are input to pull from -

i'm getting stumped, vlookup messed , i've tried change values in column text , values general. here code, can vlookups seperately need them 1 after , when first vlookup formula comes #n/a or #name or #value need 2nd vlookup formula come through , fill fields appropraitely, suggestions, i've tried many different variations. here have error says expected list seperator or (, if take of iferror , 2nd function number values work because brand names can determined serial numbers 4th , 5th digit, serials have letters in place , whole issue, have letter , dash, a- example, need able vlookup of these types not replace fields have accurate values, first vlookup replaces 2nd comes in fill errored fiels. appreciated.

this code reproduces errors.

sub work2() dim lr long dim row string row = "w2" row = "w" & x lr = range("w" & rows.count).end(xlup).row range(columns(6), columns(7)).select x = 0 2 row = "w" & x range("a2:a" & lr).formula = "=iferror(vlookup(value(" & row &  "),[gpic.xslx]sheet1!$r:$s,2,false),"=vlookup(" & row & ",[gpic.xslx]sheet1!$r:$s,2,false)")"   next   end sub 

this code can number vlookup fields cant letters right tried fix above code, said produces error

sub work2() dim lr long dim row string row = "w2" row = "w" & x lr = range("w" & rows.count).end(xlup).row range(columns(6), columns(7)).select x = 0 2 row = "w" & x range("a2:a" & lr).formula = "=vlookup(value(" & row & "),[gpic.xslx]sheet1!$r:$s,2,false)"  next     end sub 

for added info, r , s in worksheet have codes equal brand right results or #value believe.

column r column s 1        ford 2        dodge ae       hyundai 

that how vlookups work

         b        c        d        e       f     1     adam      1      adam     2     peter     3      john     3     john      4      phil     4     phil      6      #n/a           b        c        d        e       f      1     adam      1      adam       6      peter     2     peter     3      john     3     john      4      phil     4     phil      6      peter 

cell d1 contains formula: =if(iserror(index(b:b,match(c1,a:a,0))),index(f:f,match(c1,e:e,0)),index(b:b,match(c1,a:a,0))) copy down.