Error in Vlookup string: A value used in the formula is of the wrong data type.

Hi, I've been using excel for years, but have just started to expand what I do with it, so any help on my problem will be gratefully received.

As the title says, I am having a problem with Vlookup. The formula I have is: =VLOOKUP(VALUE(MID(B2,1,4))*NewMod!$A$2:$B$793,2,0)
All cells are formatted as general
Cell B2 contains a 9 digit number
All cells in column $A contain a 4 digit number
All cells in column $B contain a plain text string

I did originally have the cells in the columns containing numbers formatted as numbers and the ones containing text formatted as text, but still had the same error.

Thanks in advance

Ian

This works OK for me

=VLOOKUP(VALUE(MID(B2,1,4)),NewMod!$A$2:$B$793,2,0)

B2 is text string "123456789" or numeric

A2 is numeric 1234

Originally Posted by Taylorim

=VLOOKUP(VALUE(MID(B2,1,4))*NewMod!$A$2:$B$793,2,0)

Here's another way to write that formula:

=VLOOKUP(--LEFT(B2,4),NewMod!$A$2:$B$793,2,0)

You're welcome. We appreciate the feedback!