Trying to index/match but a substring of the source column.

[spongebob]

Hello All,

I'm trying to figure out how to match a part or substring of a column to a reference column, and return a value (cost) in column G.
Based on the match values I'm using I get "close answers or last answers" and I believe what makes the most sense, is to get a substring of the description in order to have multiple values that are common match.
So as an example, I may have 4 descriptions with "4 x $25" in column A, instead of matching full descriptions and having a huge reference table that will become outdated, I would like to just match the "4 x $25"
Any assistance appreciated!

Excel-Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item Description						Cost		Reference
2	Cool Item ( Crazy 15 )	1	159.95	$159.95		$159.95	$31.50		Code Name	Cost
3	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	$39.75	$0.00	$39.75	$31.50		9 Bundle	$ 31.50
4	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	$39.75	$0.00	$39.75	$31.50		Crazy 12	$ 48.00
5	Cool Item ( 7 x $25 Bundle ) #1	1	69.75	$69.75	$0.00	$69.75	$31.50		4 x $25	$ 18.00
6	Cool Item ( 2 x $40 ) #1	1	32	$32.00	$0.00	$32.00	$31.50		9 Bundle	$ 40.50
7	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	$39.75	$0.00	$39.75	$31.50		2 x $40	$ 12.00
8	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	$39.75	$0.00	$39.75	$31.50		7 x $25	$ 31.50
9	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	$39.75	$0.00	$39.75	$31.50
10	Cool Item ( 9 Bundle ) Look!	1	95.95	$95.95	$0.00	$95.95	$31.50
11	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	$69.75	$0.00	$69.75	$31.50
12	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	$69.75	$0.00	$69.75	$31.50
13	Cool Item ( 2 x $40 ) #1	1	32	$32.00	$0.00	$32.00	$31.50
14	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	$69.75	$0.00	$69.75	$31.50
15	Cool Item ( Crazy 12 Bundle )	1	134.95	$134.95	$0.00	$134.95	$31.50
16	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	$69.75	$0.00	$69.75	$31.50
17	Cool Item ( 4 x $25 Bundle ) Ninja	1	39.75	$39.75	$0.00	$39.75	$31.50
18	Cool Item ( 4 x $25 Bundle ) Ninja	1	39.75	$39.75	$0.00	$39.75	$31.50
19	Cool Item ( 4 x $25 Bundle ) TEST	1	39.75	$39.75	$0.00	$39.75	$31.50
20	Cool Item ( 2 x $40 ) #1	1	32	$32.00	$0.00	$32.00	$31.50
21	Cool Item ( 2 x $40 ) #1	1	32	$32.00	$0.00	$32.00	$31.50
22	Cool Item ( 2 x $40 ) #1	1	32	$32.00	$0.00	$32.00	$31.50
Sheet1

Cell Formulas
Range	Formula
F2:F22	F2	=D2+E2
G2:G22	G2	=INDEX($J$3:$J$8,MATCH(A2,$I$3:$I$8,1))
J3,J8	J3	=7*4.5
J4	J4	=2*24
J5	J5	=4*4.5
J6	J6	=9*4.5
D2:D22	D2	=IF(ISNUMBER(SEARCH("AWS",A2)),(B2*C2),C2)

 

[Zot]

But if you use partial description, then not every row will have same partial description. Then the formula will become unique for certain lines

 

[Marcelo Branco]

Maybe

G2 copied down
=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($I$3:$I$8,A2)),$J$3:$J$8),"Not found")

M.

 

[jtakw]

Hi,

Not sure I understand 100%, do you mean something like this?
And I have no idea what's happening in Column J.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item Description						Cost		Reference
2	Cool Item ( Crazy 15 )	1	159.95	159.95		159.95	Not Found		Code Name	Cost
3	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	39.75	0	39.75	18		9 Bundle	31.5
4	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	39.75	0	39.75	18		Crazy 12	48
5	Cool Item ( 7 x $25 Bundle ) #1	1	69.75	69.75	0	69.75	31.5		4 x $25	18
6	Cool Item ( 2 x $40 ) #1	1	32	32	0	32	12		9 Bundle	40.5
7	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	39.75	0	39.75	18		2 x $40	12
8	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	39.75	0	39.75	18		7 x $25	31.5
9	Cool Item ( 4 x $25 Bundle ) APJ Market	1	39.75	39.75	0	39.75	18
10	Cool Item ( 9 Bundle ) Look!	1	95.95	95.95	0	95.95	40.5
11	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	69.75	0	69.75	31.5
12	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	69.75	0	69.75	31.5
13	Cool Item ( 2 x $40 ) #1	1	32	32	0	32	12
14	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	69.75	0	69.75	31.5
15	Cool Item ( Crazy 12 Bundle )	1	134.95	134.95	0	134.95	48
16	Cool Item ( 7 x $25 Bundle ) "Lucky 7"	1	69.75	69.75	0	69.75	31.5
17	Cool Item ( 4 x $25 Bundle ) Ninja	1	39.75	39.75	0	39.75	18
18	Cool Item ( 4 x $25 Bundle ) Ninja	1	39.75	39.75	0	39.75	18
19	Cool Item ( 4 x $25 Bundle ) TEST	1	39.75	39.75	0	39.75	18
20	Cool Item ( 2 x $40 ) #1	1	32	32	0	32	12
21	Cool Item ( 2 x $40 ) #1	1	32	32	0	32	12
22	Cool Item ( 2 x $40 ) #1	1	32	32	0	32	12
Sheet916

Cell Formulas
Range	Formula
G2:G22	G2	=IFERROR(LOOKUP(2,1/SEARCH(I$3:I$8,A2),J$3:J$8),"Not Found")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[spongebob]

Thanks all, I will review these shortly and also update my profile as suggested.
Thank you!

 

[spongebob]

Thank you all for your Help!
Both Marcelo Branco & jtakw methods did work, not sure under what circumstances one would be different than the other.

 

[jtakw]

You're welcome, thanks for the feedback.

In this case, the ISNUMBER test in Marcelo Branco formula is not needed, otherwise, both formula works the same.