searching characters problem

It worked like a charm. Thanks a zillion to both of you!

However, I need to use the formula in a macro, so I input the following code:
ActiveCell.FormulaR1C1 = "=INDEX(Make!A:A,MATCH(" * "&LEFT(RC[-" & VAR4 & "],3)" & " & " * ",Make!B:B, 0))""
where VAR4 is the difference between column# of Make (column U) & column # of ID (column O), so VAR4=7.
I get an error "type mismatch". What am I doing wrong?

I think the A:A and B:B references will need to change to R1C1 syntax and also a literal " in a formula needs to be "", i.e. where the formula has "*" this needs to be ""*"".

The easisest way to do this is to use the macro recorder. record a macro in which you put that formula in the required cell and then you'll get the right syntax

Another way of getting the correct formula is to:
1. Select cell with formula.
2. In the VBE Immediate window type '? activecell.FormulaR1C1' (without the quotes)

This should display something like =INDEX(Sheet2!C[1],MATCH("*"&LEFT(RC[-18],3)&"*",Sheet2!C[-5], 0))

I ended up with the following code:
ActiveCell.FormulaR1C1 = "=INDEX(Make!A:A,MATCH(""*""&LEFT(rc[-" & Var4 & "],3)&""*"",Make!B:B,0))"
where Var4 is calculated before.
When I run the macro, The formula is correct in the formula bar, but in the cell I get "#NAME?". However, if I place the cursor at the ed of the formula bar, without changing anything and I press "Enter", I get the correct value.
What is wrong?