# Vlookup - Partial

#### jRyan2003

##### Board Regular
OS=W2K
XL=2K

Is it possible to have Vlookup work on only part of a cell entry? For example:

A1 = QWERTY 05

I would like to be able to do a lookup on QWERTY and return a value based on that.

Thanks,

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
when you create the formula leave off the last parameter.

=vlookup(a1,table data,2)
for an exact match you add
=vlookup(a1,table data,2,false)

HTH
texasalynn

Is it that you want to search for everything before the space; the first x characters, etc?

What are the rules for the partial string?

=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),Table,ColID,TF)

may work depending on your exact requirements...

texasalynn said:
when you create the formula leave off the last parameter.

=vlookup(a1,table data,2)
for an exact match you add
=vlookup(a1,table data,2,false)

HTH
texasalynn

Thanks. That worked.

I found that the easiest way to do this is to build the LEFT function into the LOOKUP.

In the example you've given, the first part of your function would look like this:

=VLOOKUP(LEFT(A1,6))

The '6' is the number of characters that you want to search for.

Hope this helps!

Regards

Paul Edmondson said:
I found that the easiest way to do this is to build the LEFT function into the LOOKUP.

In the example you've given, the first part of your function would look like this:

=VLOOKUP(LEFT(A1,6))

The '6' is the number of characters that you want to search for.

Hope this helps!

Regards

Better yet!!! Thanks...

If A1 houses the partial lookup value like QWERTY, then:

=VLOOKUP(A1&"*",Table,ColIdx,0)

would suffice if the requirement is: any value befinning with A1.

Replies
1
Views
317
Replies
5
Views
380
Replies
21
Views
534
Replies
7
Views
379
Replies
8
Views
649

1,203,075
Messages
6,053,390
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back