Using VLOOKUP with partial search values

[Matt]

I am trying to use vlookup in the following scenario and need help with the
correct formula.

Sheet 0
a b
ABC =vlookup(A1, Sheet1!1:65536, 2, false)


I am trying to return the "Peanuts" from sheet1 but it is not allowing me to
as the partial vaule from Sheet0 (ABC) does not match the value from Sheet1
(ABC123).
Sheet1
a b
ABC123 Peanuts


Any help is greatly appreciated!

 

[John C]

Is the partial value always the first 3 characters?
i.e.:
=VLOOKUP(LEFT(A1,3),Sheet1!1:1000,2,FALSE)
If not, how is excel to determine what is the 'key'?

 

[Matt]

The lookup value will always be 3 characters while the sheet1 can be a
variable of ABC, ABC1, ABC2, etc.

Left(a1,3) doesn't seem to be working.

Thanks for the quick response

 

[John C]

Assuming your table does have abc, it works just fine for me. How is the
sheet name determined?

=VLOOKUP(LEFT(A1,3),Sheet1!1:1000,2,FALSE) does work fine. Is it possible
that in your lookup table that the ABC does not have any spaces before or
after it?

 

[T. Valko]

The lookup value will always be 3 characters while

the sheet1 can be a variable of ABC, ABC1, ABC2, etc.

Well, you have a problem. A lookup_value of ABC will "match" *all* of the
above.

A1 = lookup_value = ABC

=VLOOKUP(A1&"*",Sheet1!A:B,2,0)

That will "find" whichever of these is listed first: ABC, ABC1, ABC2

 

[Matt]

This is as it appears in excel for me.

Sheet1
a b
ABC =VLOOKUP(LEFT(A1,3),Sheet2!1:65536,2,FALSE)

Sheet2
a b
ABC1 Peanuts

I am getting a #N/A error on the formula. Using Excel 2003 SP3. The
formula makes perfect sense but is not working for some reason.

 

[John C]

You say the sheet will be ABC, or ABC1, or ABC2, but then you have the lookup
value for Peanuts NOT be ABC, but ABC1, and still looking up on Sheet2. Are
you saying that the left column on sheet 2 could be any 1 of the ABC, ABC1 or
ABC2?, but you want to only lookup by the first 3 characters on sheet 2?
I would recommend a slightly different setup then. On sheet 2, I would
insert a row before row A, and type the following in A1, and copy down as
needed:
A1: =IF(B1="","",LEFT(B1,3))
Then your lookup will be fine.
OR, you could majorly slow down your lookup and enter the following as an
array formula for your lookup: (CTRL+Shift+Enter instead of just enter).
=VLOOKUP(LEFT(A1,3),LEFT(Sheet3!1:1000,3),2,FALSE)
I strongly discourage this option