spongebob
Board Regular
- Joined
- Oct 25, 2004
- Messages
- 68
- Office Version
- 2019
- Platform
- 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!
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) |
