Two problems I see, you say that you have a 5 character code, and your
VLOOKUP("ABCD",Left(A1:B200,5),2,0)
is trying to match 4 characters (ABCD) against 5 characters in the table,
either change the "ABCD" to valid 5-character lookup, or change ,5) to ,4).

Also, the formula needs to be entered as an array formula. You do this by
commiting the formula with [Ctrl]+[Shift]+[Enter] instead of the normal
[Enter] key. When you do it right, Excel will add { and } around the
formula. If you have to edit the formula later, you have to again commit it
with the 3-key combination.

Seems after that, things work well, and I think the variation you have of it
using FIND() will probably work also.

Hope this helps.

"Jneel" wrote:

Hi, need help. I have searched the discussion threads and, in theory, found
what I needed, however it will not work for me. I get a #value
No matter what I do, I cannot get rid of the #value. I found the below
function formulas that I need and do not work for me.

I have a 5 character code in one worksheet and on the other worksheet, that
code is followed by a - and then a name. I just want to use a LEFT to gather
the needed data.

Does anyone know what I am missing here?

=Vlookup("ABCD",LEFT(A1:B200,5),2,0)

=VLOOKUP(Left(A1,FIND(",",A1)-1),YourTableOfData,YourValueToReturnCol,0)
That will take the EE-A from "EE-A, FlexMed ($25.00)" and find that value in
your table.
Thanks for your help.