# Lookup on partial text value

#### Snuffepuff

##### New Member
Hi,

I'm trying to get my spread sheet to return a value based on only a part of a the provided text.

In cell A1 I got the full name of a product, ABC-123. ABC is the model name, which costs a specific amount that I want to extract with a Lookup.

 Product Cost? Series Cost ABC-123 ABC 100

<tbody>
</tbody>

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

I've been trying to sort this with MATCH unsecsessfully. Any options?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
=VLOOKUP(LEFT(A2,3),\$C\$2:\$D\$2,2,0)

If you are going to have products with different string lengths then you need to change the lookup to something like (assuming the dash will be a unique separator:
=VLOOKUP(LEFT(A2,FIND("-",A2)-1),\$C\$2:\$D\$2,2,0)

Thanks!

I see now that I made a misstake putting the dash in as a separator. The product name is ABC123, not ABC-123 so there's no separator at all. The main issue I got is that all the product are not the same length. Same might be named ABC12345 and others ABC12. Also the difference so sometimes it might be AB, othertimes it's ABCD.

It's because of this I can't find any simple solution.

If the combination is always text followed by numbers then you can separate these into two separate (helper) columns.
E.g. in Cell A2 say we have ABC123
In B2 enter =Left(A2,min(Find({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
The above should give ABC
In C2 enter =Right(A2,len(A2)-min(find({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
The above should give 123

The just perform a standard Vlookup on cell B2. (Adjust your ranges as necessary).

Hi, does this help at all?

Excel Workbook
ABCDE
1ProductCost?SeriesCost
2ABC-123100ABC100
3GHI123456710DEF50
4DEF99950GHI10
5JLK5
Sheet1

Hi, does this help at all?

Sheet1

 * A B C D E 1 Product Cost? * Series Cost 2 ABC-123 100 * ABC 100 3 GHI1234567 10 * DEF 50 4 DEF999 50 * GHI 10 5 * * * JLK 5

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:93px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula B2 =LOOKUP(1,1/COUNTIF(A2,\$D\$2:\$D\$5&"*"),\$E\$2:\$E\$5)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked out when I put in extra cells (like A in the example). Many thanks!

Replies
9
Views
465
Replies
2
Views
58
Replies
2
Views
287
Replies
18
Views
1K
Replies
1
Views
402

1,221,127
Messages
6,158,099
Members
451,464
Latest member
Holden3

### 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.

### Which adblocker are you using?

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

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