If there are not more than 2 letters following and no preceding letters
Try something like
=IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")),
IF(ISNUMBER( value(left(B7,len(b7)-1) ),(IF(value(left(B7,len(b7)-1)4000,
VLOOKUP(value(left(B7,len(b7)-1),Sheet1!A2:B263,2), "S")), IF(ISNUMBER(
value(left(B7,len(b7)-2) ),(IF(value(left(B7,len(b7)-2)4000,
VLOOKUP(value(left(B7,len(b7)-2),Sheet1!A2:B263,2), "S")),"S"))))

(I lost track of the parenthsis. You may need more or less of them at the
end)


"jeremy via OfficeKB.com" wrote:

This funtion works for me, if there are no letters behind numbers

=IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), "S")

but some of the numbers have a letter or two behind them, which I want to
ignore (eg. 4123HG i want to recognize as 4123)....

Any Help?

jeremy

--
Message posted via http://www.officekb.com