Lookup on partial text value

[Snuffepuff]

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.

[TABLE="width: 269"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Cost?[/TD]
[TD]Series[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]ABC-123[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[anglais428]

=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)

 

[Snuffepuff]

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.

 

[anglais428]

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

 

[FormR]

Hi, does this help at all?

Excel Workbook
	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
Sheet1

 

[Snuffepuff]

Hi, does this help at all?

Sheet1

*	A	B	C	D	E
Product	Cost?	*	Series	Cost
ABC-123	*	ABC
GHI1234567	*	DEF
DEF999	*	GHI
*	*	*	JLK

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]5[/TD]

</tbody>

Spreadsheet Formulas

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!