Vlookup, if no match search another sheet using first 7 characters

[Mike820]

Can someone help me with a formula; I’d like to run a Vlookup For values in column J against a column in another sheet. If nothing is found then Vlookup only using the first 7 characters in column J to a column in another sheet and only it’s first 7 characters.

thanks,

 

[Joe4]

Are the values on the other sheet more than 7 characters?
If so, can you insert a new column to the left of that column, that takes just the first 7 characters, i.e.
If the value you are matching against is in column A originally, insert a column before it and use this formula:
=LEFT(A1,7)

If you can do that, then you can do your VLOOKUP structured something like this:

=IFERROR(VLOOKUP(J1,Sheet2!B:C,...),VLOOKUP(LEFT(J1,7),Sheet2!A:C,...))

 

[DanteAmor]

Try this

=IFERROR(VLOOKUP(J1,sheet4!A:B,2,0),IFERROR(VLOOKUP(LEFT(J1,7)&"*",Sheet5!A:B,2,0),"Dont exists"))