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

spongebob

New Member
Joined
Oct 25, 2004
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJ
1Item DescriptionCostReference
2Cool Item ( Crazy 15 )1159.95$159.95$159.95$31.50Code NameCost
3Cool Item ( 4 x $25 Bundle ) APJ Market139.75$39.75$0.00$39.75$31.509 Bundle$ 31.50
4Cool Item ( 4 x $25 Bundle ) APJ Market139.75$39.75$0.00$39.75$31.50Crazy 12$ 48.00
5Cool Item ( 7 x $25 Bundle ) #1169.75$69.75$0.00$69.75$31.504 x $25$ 18.00
6Cool Item ( 2 x $40 ) #1132$32.00$0.00$32.00$31.509 Bundle$ 40.50
7Cool Item ( 4 x $25 Bundle ) APJ Market139.75$39.75$0.00$39.75$31.502 x $40$ 12.00
8Cool Item ( 4 x $25 Bundle ) APJ Market139.75$39.75$0.00$39.75$31.507 x $25$ 31.50
9Cool Item ( 4 x $25 Bundle ) APJ Market139.75$39.75$0.00$39.75$31.50
10Cool Item ( 9 Bundle ) Look!195.95$95.95$0.00$95.95$31.50
11Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.75$69.75$0.00$69.75$31.50
12Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.75$69.75$0.00$69.75$31.50
13Cool Item ( 2 x $40 ) #1132$32.00$0.00$32.00$31.50
14Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.75$69.75$0.00$69.75$31.50
15Cool Item ( Crazy 12 Bundle )1134.95$134.95$0.00$134.95$31.50
16Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.75$69.75$0.00$69.75$31.50
17Cool Item ( 4 x $25 Bundle ) Ninja139.75$39.75$0.00$39.75$31.50
18Cool Item ( 4 x $25 Bundle ) Ninja139.75$39.75$0.00$39.75$31.50
19Cool Item ( 4 x $25 Bundle ) TEST139.75$39.75$0.00$39.75$31.50
20Cool Item ( 2 x $40 ) #1132$32.00$0.00$32.00$31.50
21Cool Item ( 2 x $40 ) #1132$32.00$0.00$32.00$31.50
22Cool Item ( 2 x $40 ) #1132$32.00$0.00$32.00$31.50
Sheet1
Cell Formulas
RangeFormula
F2:F22F2=D2+E2
G2:G22G2=INDEX($J$3:$J$8,MATCH(A2,$I$3:$I$8,1))
J3,J8J3=7*4.5
J4J4=2*24
J5J5=4*4.5
J6J6=9*4.5
D2:D22D2=IF(ISNUMBER(SEARCH("AWS",A2)),(B2*C2),C2)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,618
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe

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

M.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJ
1Item DescriptionCostReference
2Cool Item ( Crazy 15 )1159.95159.95159.95Not FoundCode NameCost
3Cool Item ( 4 x $25 Bundle ) APJ Market139.7539.75039.75189 Bundle31.5
4Cool Item ( 4 x $25 Bundle ) APJ Market139.7539.75039.7518Crazy 1248
5Cool Item ( 7 x $25 Bundle ) #1169.7569.75069.7531.54 x $2518
6Cool Item ( 2 x $40 ) #113232032129 Bundle40.5
7Cool Item ( 4 x $25 Bundle ) APJ Market139.7539.75039.75182 x $4012
8Cool Item ( 4 x $25 Bundle ) APJ Market139.7539.75039.75187 x $2531.5
9Cool Item ( 4 x $25 Bundle ) APJ Market139.7539.75039.7518
10Cool Item ( 9 Bundle ) Look!195.9595.95095.9540.5
11Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.7569.75069.7531.5
12Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.7569.75069.7531.5
13Cool Item ( 2 x $40 ) #11323203212
14Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.7569.75069.7531.5
15Cool Item ( Crazy 12 Bundle )1134.95134.950134.9548
16Cool Item ( 7 x $25 Bundle ) "Lucky 7"169.7569.75069.7531.5
17Cool Item ( 4 x $25 Bundle ) Ninja139.7539.75039.7518
18Cool Item ( 4 x $25 Bundle ) Ninja139.7539.75039.7518
19Cool Item ( 4 x $25 Bundle ) TEST139.7539.75039.7518
20Cool Item ( 2 x $40 ) #11323203212
21Cool Item ( 2 x $40 ) #11323203212
22Cool Item ( 2 x $40 ) #11323203212
Sheet916
Cell Formulas
RangeFormula
G2:G22G2=IFERROR(LOOKUP(2,1/SEARCH(I$3:I$8,A2),J$3:J$8),"Not Found")
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 25, 2004
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
Thanks all, I will review these shortly and also update my profile as suggested.
Thank you!
 

spongebob

New Member
Joined
Oct 25, 2004
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,613
Messages
5,707,402
Members
421,508
Latest member
Jalayne

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?

Disable AdBlock

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
Top