partial match in vlookup with table array

[Jneel]

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.

 

[JLatham]

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.

 

[T. Valko]

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

I have a 5 character code in one worksheet and
on the other worksheet, that code is followed by
a - and then a name.

Try it like this:

=INDEX(B1:B200,MATCH("abcde-*",A1:A200,0))

Or, using a cell to hold the lookup value:

D1 = abcde

=INDEX(B1:B200,MATCH(D1&"-*",A1:A200,0))

 

[Jneel]

Thanks, yea I saw that I left of the fifth character in my example.

entering as an array worked - sort of, I may be asking for too much, however
the result only filled in the 5 characters, I wanted the return to be
exactly what was in the column, with the code - name.

Is that not possible?

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.

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.