vlookup Lookup Value Question

[dodgemain]

Hi,
Having trouble getting the below formula to work the way I want it. My lookup value A1 is 1.00 BB. It seems the formula does not like the text in the cell but I would really like to leave it in there. I tried replacing A1 with the formula =left(A1,4) but that did not work. I really don't want to divide the cells into separate columns. Any ideas?
Thanks in advance!!!


=VLOOKUP(A1,A10:F20,3)

[Richard Buttrey]

Have you tried adding the 4th 'False' parameter to the VLOOKUP?

[daffodil11]

=lookup(2,1/(left(a10:f20,4)*1=c1),c10:c20)

[dodgemain]

Thanks for the help. But, can't get either suggestion to work.

[Punnam]

=value(left(A1,4))

try this not sure whether its works or not

Punnam

[Richard Buttrey]

Thanks for the help. But, can't get either suggestion to work.

I think it's time to upload the workbook so that we can see the request in context.

[dodgemain]

Adding a +0 to the left function made the formula work for me. Thanks for all the help.

=VLOOKUP(LEFT(A1,4)+0,A10:F20,3)