Extract text from a string of text based on the range defined

H

holyvulcan

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I am having an issue in extracting a text from a string in a cell. below is the two tables 1 and 2.

Table 2 is the table where all the information is available. What I wanted to do in Table 1 is to extract the brand and type name from the Product Name. I managed to do so for the brand using the formula - INDEX(Table!$C$2:$C$69,MATCH(1,ISNUMBER(SEARCH(Table!$C$2:$C$69,G2))*1,0)) but I am not able to do the same for the type as the brands have similar types.

I was trying to do something like, matching the category, sub-category and the brand and then display the type after searching the types in the 4th column in Table Sheet.

Hope you can help me in resolving this.

Thank you in advance.

Best Regards,
Brijesh



Table 1:

Sample-sales-data.xlsx
ABCDEF
1Product IDCategorySub-CategoryBrandTypeProduct Name
2TEC-MA-10001047TechnologyMachines3D Systems3D Systems Cube Printer, 2nd Generation, Magenta
3TEC-MA-10001047TechnologyMachines3D Systems3D Systems Cube Printer, 2nd Generation, Magenta
4TEC-MA-10002073TechnologyMachines3D Systems3D Systems Cube Printer, 2nd Generation, White
5TEC-MA-10002073TechnologyMachines3D Systems3D Systems Cube Printer, 2nd Generation, White
6TEC-PH-10001363TechnologyPhonesAppleApple iPhone 5S
7TEC-PH-10001061TechnologyPhonesAppleApple iPhone 5C
8TEC-PH-10001061TechnologyPhonesAppleApple iPhone 5C
9TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
10TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
11TEC-PH-10002350TechnologyPhonesAppleApple EarPods with Remote and Mic
12TEC-PH-10002350TechnologyPhonesAppleApple EarPods with Remote and Mic
13TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
14TEC-PH-10001363TechnologyPhonesAppleApple iPhone 5S
15TEC-PH-10002350TechnologyPhonesAppleApple EarPods with Remote and Mic
16TEC-PH-10001363TechnologyPhonesAppleApple iPhone 5S
17TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
18TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
19TEC-PH-10001061TechnologyPhonesAppleApple iPhone 5C
20TEC-PH-10002885TechnologyPhonesAppleApple iPhone 5
21TEC-PH-10001363TechnologyPhonesAppleApple iPhone 5S
22TEC-PH-10002350TechnologyPhonesAppleApple EarPods with Remote and Mic
23TEC-PH-10001061TechnologyPhonesAppleApple iPhone 5C
24TEC-PH-10004536TechnologyPhonesAvayaAvaya 5420 Digital phone
25TEC-PH-10004536TechnologyPhonesAvayaAvaya 5420 Digital phone
26TEC-PH-10001198TechnologyPhonesAvayaAvaya 4621SW VoIP phone
27TEC-PH-10002352TechnologyPhonesAvayaAvaya IP Phone 1140E VoIP phone
28TEC-PH-10004536TechnologyPhonesAvayaAvaya 5420 Digital phone
29TEC-PH-10001198TechnologyPhonesAvayaAvaya 4621SW VoIP phone
30TEC-PH-10001198TechnologyPhonesAvayaAvaya 4621SW VoIP phone
31TEC-PH-10004080TechnologyPhonesAvayaAvaya 5410 Digital phone
32TEC-PH-10004080TechnologyPhonesAvayaAvaya 5410 Digital phone
33TEC-PH-10001198TechnologyPhonesAvayaAvaya 4621SW VoIP phone
34TEC-PH-10004080TechnologyPhonesAvayaAvaya 5410 Digital phone
35TEC-PH-10004080TechnologyPhonesAvayaAvaya 5410 Digital phone
36TEC-PH-10001949TechnologyPhonesCiscoCisco SPA 501G IP Phone
37TEC-MA-10000864TechnologyMachinesCiscoCisco 9971 IP Video Phone Charcoal
38TEC-PH-10001433TechnologyPhonesCiscoCisco Small Business SPA 502G VoIP phone
39TEC-PH-10000149TechnologyPhonesCiscoCisco SPA525G2 IP Phone - Wireless
40TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
41TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
42TEC-MA-10004212TechnologyMachinesCiscoCisco SPA525G2 5-Line IP Phone
43TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
44TEC-PH-10000149TechnologyPhonesCiscoCisco SPA525G2 IP Phone - Wireless
45TEC-MA-10002178TechnologyMachinesCiscoCisco CP-7937G Unified IP Conference Station Phone
46TEC-PH-10003580TechnologyPhonesCiscoCisco IP Phone 7961G-GE VoIP phone
47TEC-PH-10004345TechnologyPhonesCiscoCisco SPA 502G IP Phone
48TEC-PH-10000149TechnologyPhonesCiscoCisco SPA525G2 IP Phone - Wireless
49TEC-PH-10001433TechnologyPhonesCiscoCisco Small Business SPA 502G VoIP phone
50TEC-MA-10002412TechnologyMachinesCiscoCisco TelePresence System EX90 Videoconferencing Unit
51TEC-MA-10002178TechnologyMachinesCiscoCisco CP-7937G Unified IP Conference Station Phone
52TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
53TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
54TEC-PH-10004912TechnologyPhonesCiscoCisco SPA112 2 Port Phone Adapter
55TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
56TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
57TEC-PH-10004434TechnologyPhonesCiscoCisco IP Phone 7961G VoIP phone - Dark gray
58TEC-MA-10000864TechnologyMachinesCiscoCisco 9971 IP Video Phone Charcoal
59TEC-MA-10001570TechnologyMachinesCiscoCisco Desktop Collaboration Experience DX650 IP Video Phone
60TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
61TEC-PH-10003885TechnologyPhonesCiscoCisco SPA508G
62TEC-PH-10004345TechnologyPhonesCiscoCisco SPA 502G IP Phone
63TEC-PH-10000149TechnologyPhonesCiscoCisco SPA525G2 IP Phone - Wireless
64TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
65TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
66TEC-PH-10004345TechnologyPhonesCiscoCisco SPA 502G IP Phone
67TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
68TEC-PH-10004345TechnologyPhonesCiscoCisco SPA 502G IP Phone
69TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
70TEC-PH-10003885TechnologyPhonesCiscoCisco SPA508G
71TEC-PH-10003580TechnologyPhonesCiscoCisco IP Phone 7961G-GE VoIP phone
72TEC-PH-10000149TechnologyPhonesCiscoCisco SPA525G2 IP Phone - Wireless
73TEC-PH-10001433TechnologyPhonesCiscoCisco Small Business SPA 502G VoIP phone
74TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
75TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
76TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
77TEC-PH-10003580TechnologyPhonesCiscoCisco IP Phone 7961G-GE VoIP phone
78TEC-PH-10003885TechnologyPhonesCiscoCisco SPA508G
79TEC-PH-10004434TechnologyPhonesCiscoCisco IP Phone 7961G VoIP phone - Dark gray
80TEC-PH-10004345TechnologyPhonesCiscoCisco SPA 502G IP Phone
81TEC-PH-10004912TechnologyPhonesCiscoCisco SPA112 2 Port Phone Adapter
82TEC-PH-10003580TechnologyPhonesCiscoCisco IP Phone 7961G-GE VoIP phone
83TEC-PH-10001433TechnologyPhonesCiscoCisco Small Business SPA 502G VoIP phone
84TEC-PH-10001530TechnologyPhonesCiscoCisco Unified IP Phone 7945G VoIP phone
85TEC-MA-10003589TechnologyMachinesCiscoCisco 8961 IP Phone Charcoal
86TEC-PH-10004667TechnologyPhonesCiscoCisco 8x8 Inc. 6753i IP Business Phone System
87TEC-PH-10004434TechnologyPhonesCiscoCisco IP Phone 7961G VoIP phone - Dark gray
88TEC-PH-10004912TechnologyPhonesCiscoCisco SPA112 2 Port Phone Adapter
89TEC-PH-10002496TechnologyPhonesCiscoCisco SPA301
90TEC-CO-10001449TechnologyCopiersHewlett PackardHewlett Packard LaserJet 3310 Copier
91TEC-CO-10002095TechnologyCopiersHewlett PackardHewlett Packard 610 Color Digital Copier / Printer
92TEC-CO-10002095TechnologyCopiersHewlett PackardHewlett Packard 610 Color Digital Copier / Printer
93TEC-CO-10002095TechnologyCopiersHewlett PackardHewlett Packard 610 Color Digital Copier / Printer
94TEC-CO-10001449TechnologyCopiersHewlett PackardHewlett Packard LaserJet 3310 Copier
95TEC-CO-10000971TechnologyCopiersHewlett PackardHewlett Packard 310 Color Digital Copier
96TEC-CO-10001449TechnologyCopiersHewlett PackardHewlett Packard LaserJet 3310 Copier
97TEC-CO-10002095TechnologyCopiersHewlett PackardHewlett Packard 610 Color Digital Copier / Printer
98TEC-CO-10000971TechnologyCopiersHewlett PackardHewlett Packard 310 Color Digital Copier
99TEC-CO-10000971TechnologyCopiersHewlett PackardHewlett Packard 310 Color Digital Copier
100TEC-CO-10001449TechnologyCopiersHewlett PackardHewlett Packard LaserJet 3310 Copier
101TEC-CO-10002095TechnologyCopiersHewlett PackardHewlett Packard 610 Color Digital Copier / Printer
102TEC-AC-10003027TechnologyAccessoriesImationImation 8GB Mini TravelDrive USB 2.0 Flash Drive
103TEC-AC-10002167TechnologyAccessoriesImationImation 8gb Micro Traveldrive Usb 2.0 Flash Drive
104TEC-AC-10004659TechnologyAccessoriesImationImation Secure+ Hardware Encrypted USB 2.0 Flash Drive; 16GB
105TEC-AC-10001445TechnologyAccessoriesImationImation USB 2.0 Swivel Flash Drive USB flash drive - 4 GB - Pink
106TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
107TEC-AC-10001267TechnologyAccessoriesImationImation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P ...
108TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
109TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
110TEC-AC-10002253TechnologyAccessoriesImationImation Bio 8GB USB Flash Drive Imation Corp
111TEC-AC-10001267TechnologyAccessoriesImationImation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P ...
112TEC-AC-10002167TechnologyAccessoriesImationImation 8gb Micro Traveldrive Usb 2.0 Flash Drive
113TEC-AC-10004659TechnologyAccessoriesImationImation Secure+ Hardware Encrypted USB 2.0 Flash Drive; 16GB
114TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
115TEC-AC-10003027TechnologyAccessoriesImationImation 8GB Mini TravelDrive USB 2.0 Flash Drive
116TEC-AC-10002253TechnologyAccessoriesImationImation Bio 8GB USB Flash Drive Imation Corp
117TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
118TEC-AC-10002217TechnologyAccessoriesImationImation Clip USB flash drive - 8 GB
119TEC-AC-10004659TechnologyAccessoriesImationImation Secure+ Hardware Encrypted USB 2.0 Flash Drive; 16GB
120TEC-AC-10004659TechnologyAccessoriesImationImation Secure+ Hardware Encrypted USB 2.0 Flash Drive; 16GB
121TEC-AC-10002253TechnologyAccessoriesImationImation Bio 8GB USB Flash Drive Imation Corp
122TEC-AC-10000358TechnologyAccessoriesImationImation Secure Drive + Hardware Encrypted USB flash drive - 16 GB
123TEC-AC-10000358TechnologyAccessoriesImationImation Secure Drive + Hardware Encrypted USB flash drive - 16 GB
124TEC-AC-10002217TechnologyAccessoriesImationImation Clip USB flash drive - 8 GB
125TEC-AC-10001267TechnologyAccessoriesImationImation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P ...
126TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
127TEC-AC-10002217TechnologyAccessoriesImationImation Clip USB flash drive - 8 GB
128TEC-AC-10000990TechnologyAccessoriesImationImation Bio 2GB USB Flash Drive Imation Corp
129TEC-AC-10002558TechnologyAccessoriesImationImation Swivel Flash Drive USB flash drive - 8 GB
130TEC-AC-10003027TechnologyAccessoriesImationImation 8GB Mini TravelDrive USB 2.0 Flash Drive
131TEC-AC-10004877TechnologyAccessoriesImationImation 30456 USB Flash Drive 8GB
132TEC-AC-10001267TechnologyAccessoriesImationImation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P ...
133TEC-AC-10003832TechnologyAccessoriesImationImation 16GB Mini TravelDrive USB 2.0 Flash Drive
134TEC-AC-10002253TechnologyAccessoriesImationImation Bio 8GB USB Flash Drive Imation Corp
135TEC-AC-10004877TechnologyAccessoriesImationImation 30456 USB Flash Drive 8GB
136TEC-AC-10003038TechnologyAccessoriesKingstonKingston Digital DataTraveler 16GB USB 2.0
137TEC-AC-10003441TechnologyAccessoriesKingstonKingston Digital DataTraveler 32GB USB 2.0
138TEC-AC-10003038TechnologyAccessoriesKingstonKingston Digital DataTraveler 16GB USB 2.0
139TEC-AC-10003038TechnologyAccessoriesKingstonKingston Digital DataTraveler 16GB USB 2.0
140TEC-AC-10000199TechnologyAccessoriesKingstonKingston Digital DataTraveler 8GB USB 2.0
141TEC-AC-10001998TechnologyAccessoriesLogitechLogitech LS21 Speaker System - PC Multimedia - 2.1-CH - Wired
142TEC-AC-10001552TechnologyAccessoriesLogitechLogitech K350 2.4Ghz Wireless Keyboard
143TEC-AC-10000844TechnologyAccessoriesLogitechLogitech Gaming G510s - Keyboard
144TEC-AC-10003832TechnologyAccessoriesLogitechLogitech P710e Mobile Speakerphone
145TEC-AC-10001109TechnologyAccessoriesLogitechLogitech Trackman Marble Mouse
146TEC-AC-10002049TechnologyAccessoriesLogitechLogitech G19 Programmable Gaming Keyboard
147TEC-AC-10003023TechnologyAccessoriesLogitechLogitech G105 Gaming Keyboard
148TEC-AC-10001013TechnologyAccessoriesLogitechLogitech ClearChat Comfort/USB Headset H390
149TEC-AC-10000736TechnologyAccessoriesLogitechLogitech G600 MMO Gaming Mouse
150TEC-AC-10001013TechnologyAccessoriesLogitechLogitech ClearChat Comfort/USB Headset H390
151TEC-AC-10002049TechnologyAccessoriesLogitechLogitech G19 Programmable Gaming Keyboard
152TEC-AC-10004145TechnologyAccessoriesLogitechLogitech diNovo Edge Keyboard
153TEC-AC-10003095TechnologyAccessoriesLogitechLogitech G35 7.1-Channel Surround Sound Headset
154TEC-AC-10002637TechnologyAccessoriesLogitechLogitech VX Revolution Cordless Laser Mouse for Notebooks (Black)
155TEC-AC-10002049TechnologyAccessoriesLogitechLogitech G19 Programmable Gaming Keyboard
156TEC-AC-10000580TechnologyAccessoriesLogitechLogitech G13 Programmable Gameboard with LCD Display
157TEC-AC-10000844TechnologyAccessoriesLogitechLogitech Gaming G510s - Keyboard
158TEC-AC-10004145TechnologyAccessoriesLogitechLogitech diNovo Edge Keyboard
159TEC-AC-10002567TechnologyAccessoriesLogitechLogitech G602 Wireless Gaming Mouse
160TEC-AC-10001109TechnologyAccessoriesLogitechLogitech Trackman Marble Mouse
161TEC-AC-10002567TechnologyAccessoriesLogitechLogitech G602 Wireless Gaming Mouse
162TEC-PH-10002923TechnologyPhonesLogitechLogitech B530 USB Headset - headset - Full size, Binaural
163TEC-AC-10000736TechnologyAccessoriesLogitechLogitech G600 MMO Gaming Mouse
164TEC-AC-10002567TechnologyAccessoriesLogitechLogitech G602 Wireless Gaming Mouse
165TEC-AC-10001714TechnologyAccessoriesLogitechLogitech MX Performance Wireless Mouse
166TEC-AC-10001539TechnologyAccessoriesLogitechLogitech G430 Surround Sound Gaming Headset with Dolby 7.1 Technology
167TEC-PH-10002275TechnologyPhonesMitelMitel 5320 IP Phone VoIP phone
168TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
169TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
170TEC-PH-10002275TechnologyPhonesMitelMitel 5320 IP Phone VoIP phone
171TEC-PH-10002275TechnologyPhonesMitelMitel 5320 IP Phone VoIP phone
172TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
173TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
174TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
175TEC-PH-10002275TechnologyPhonesMitelMitel 5320 IP Phone VoIP phone
176TEC-PH-10004165TechnologyPhonesMitelMitel MiVoice 5330e IP Phone
177TEC-MA-10001972TechnologyMachinesOkidataOkidata C331dn Printer
178TEC-MA-10001972TechnologyMachinesOkidataOkidata C331dn Printer
179TEC-MA-10001972TechnologyMachinesOkidataOkidata C331dn Printer
180TEC-MA-10001148TechnologyMachinesOkidataOkidata MB491 Multifunction Printer
181TEC-MA-10001972TechnologyMachinesOkidataOkidata C331dn Printer
182TEC-MA-10003176TechnologyMachinesOkidataOkidata B400 Printer
183TEC-MA-10003230TechnologyMachinesOkidataOkidata C610n Printer
184TEC-MA-10001856TechnologyMachinesOkidataOkidata C610n Printer
185TEC-MA-10001148TechnologyMachinesOkidataOkidata MB491 Multifunction Printer
186TEC-MA-10000984TechnologyMachinesOkidataOkidata MB760 Printer
187TEC-MA-10003176TechnologyMachinesOkidataOkidata B400 Printer
188TEC-MA-10003337TechnologyMachinesOkidataOkidata B401 Printer
189TEC-MA-10000984TechnologyMachinesOkidataOkidata MB760 Printer
190TEC-PH-10002680TechnologyPhonesSamsungSamsung Galaxy Note 3
191TEC-PH-10001536TechnologyPhonesSamsungSpigen Samsung Galaxy S5 Case Wallet
192TEC-PH-10003095TechnologyPhonesSamsungSamsung HM1900 Bluetooth Headset
193TEC-PH-10003095TechnologyPhonesSamsungSamsung HM1900 Bluetooth Headset
194TEC-PH-10000730TechnologyPhonesSamsungSamsung Galaxy S4 Active
195TEC-PH-10002564TechnologyPhonesSamsungOtterBox Defender Series Case - Samsung Galaxy S4
196TEC-PH-10000560TechnologyPhonesSamsungSamsung Galaxy S III - 16GB - pebble blue (T-Mobile)
197TEC-PH-10003655TechnologyPhonesSamsungSannysis Cute Owl Design Soft Skin Case Cover for Samsung Galaxy S4
198TEC-PH-10002564TechnologyPhonesSamsungOtterBox Defender Series Case - Samsung Galaxy S4
199TEC-PH-10001536TechnologyPhonesSamsungSpigen Samsung Galaxy S5 Case Wallet
200TEC-PH-10003095TechnologyPhonesSamsungSamsung HM1900 Bluetooth Headset
201TEC-PH-10001750TechnologyPhonesSamsungSamsung Rugby III
202TEC-PH-10001750TechnologyPhonesSamsungSamsung Rugby III
203TEC-PH-10001750TechnologyPhonesSamsungSamsung Rugby III
204TEC-PH-10002624TechnologyPhonesSamsungSamsung Galaxy S4 Mini
205TEC-PH-10001459TechnologyPhonesSamsungSamsung Galaxy Mega 6.3
206TEC-PH-10000560TechnologyPhonesSamsungSamsung Galaxy S III - 16GB - pebble blue (T-Mobile)
207TEC-PH-10004188TechnologyPhonesSamsungOtterBox Commuter Series Case - Samsung Galaxy S4
208TEC-PH-10000562TechnologyPhonesSamsungSamsung Convoy 3
209TEC-PH-10003095TechnologyPhonesSamsungSamsung HM1900 Bluetooth Headset
210TEC-PH-10002564TechnologyPhonesSamsungOtterBox Defender Series Case - Samsung Galaxy S4
211TEC-PH-10002564TechnologyPhonesSamsungOtterBox Defender Series Case - Samsung Galaxy S4
212TEC-PH-10001459TechnologyPhonesSamsungSamsung Galaxy Mega 6.3
213TEC-PH-10003442TechnologyPhonesSamsungSamsung Replacement EH64AVFWE Premium Headset
214TEC-AC-10000109TechnologyAccessoriesSonySony Micro Vault Click 16 GB USB 2.0 Flash Drive
215TEC-AC-10004708TechnologyAccessoriesSonySony 32GB Class 10 Micro SDHC R40 Memory Card
216TEC-AC-10004803TechnologyAccessoriesSonySony Micro Vault Click 4 GB USB 2.0 Flash Drive
217TEC-AC-10002380TechnologyAccessoriesSonySony 8GB Class 10 Micro SDHC R40 Memory Card
218TEC-AC-10001101TechnologyAccessoriesSonySony 16GB Class 10 Micro SDHC R40 Memory Card
219TEC-AC-10000109TechnologyAccessoriesSonySony Micro Vault Click 16 GB USB 2.0 Flash Drive
Main Sheet
Cell Formulas
RangeFormula
D2:D219D2=INDEX(Table!$C$2:$C$69,MATCH(1,ISNUMBER(SEARCH(Table!$C$2:$C$69,F2))*1,0))



Table 2:


Sample-sales-data.xlsx
BCDE
1CategoryBrandSub-CategoryType
2Technology3D SystemsMachinesCube Printer
3TechnologyAastraPhonesVoIP Phone
4TechnologyAmazonBasicsAccessoriesWired Mouse
5TechnologyApplePhonesEarPods
6TechnologyApplePhonesMobile
7TechnologyAT&TMachinesLandline Handset
8TechnologyAT&TPhones
9TechnologyAT&TPhonesLandline Handset
10TechnologyAvayaPhonesVoIP Phone
11TechnologyBelkinAccessoriesKeyboard
12TechnologyBelkinPhonesCable
13TechnologyBelkinPhonesCase
14TechnologyBelkinPhonesCover
15TechnologyCanonCopiersCopier
16TechnologyCanonMachinesCopier
17TechnologyCiscoMachines
18TechnologyCiscoMachinesCase
19TechnologyCiscoMachinesIP Phone
20TechnologyCiscoMachinesIP Video Phone
21TechnologyCiscoPhones
22TechnologyCiscoPhonesAdapter
23TechnologyCiscoPhonesIP Phone
24TechnologyCiscoPhonesVoIP Phone
25TechnologyEnermaxAccessoriesKeyboard
26TechnologyEnermaxAccessoriesWireless Keyboard
27TechnologyEnermaxAccessoriesWireless Keyboard and Mouse
28TechnologyHewlett PackardCopiersCopier
29TechnologyHewlett PackardMachinesCalculator
30TechnologyHewlett PackardMachinesCopier
31TechnologyHewlett PackardMachinesPrinter
32TechnologyImationAccessoriesUSB 2.0 Flash Drive
33TechnologyImationAccessoriesUSB 3.0 Flash Drive
34TechnologyImationAccessoriesUSB flash drive
35TechnologyKingstonAccessoriesDataTraveller
36TechnologyLGPhones
37TechnologyLGPhonesHeadset
38TechnologyLogitechAccessoriesDisplay
39TechnologyLogitechAccessoriesGaming Mouse
40TechnologyLogitechAccessoriesHeadset
41TechnologyLogitechAccessoriesKeyboard
42TechnologyLogitechAccessoriesMarble Mouse
43TechnologyLogitechAccessoriesMouse
44TechnologyLogitechAccessoriesSpeaker
45TechnologyLogitechAccessoriesSpeakerphone
46TechnologyLogitechAccessoriesWireless Keyboard
47TechnologyLogitechAccessoriesWireless Keyboard and Mouse
48TechnologyLogitechAccessoriesWireless Mouse
49TechnologyLogitechPhonesHeadset
50TechnologyLogitechPhonesSpeakerphone
51TechnologyMemorexAccessories
52TechnologyMemorexAccessoriesFlash Drive
53TechnologyMemorexAccessoriesTravel Drive
54TechnologyMemorexAccessoriesUSB 2.0 Flash Drive
55TechnologyMitelPhonesIP Phone
56TechnologyMitelPhonesVoIP Phone
57TechnologyOkidataMachinesMultifunction Printer
58TechnologyOkidataMachinesPrinter
59TechnologyPanasonicMachinesMultifunction Printer
60TechnologyPanasonicPhones
61TechnologyPanasonicPhonesCase
62TechnologyPanasonicPhonesphone
63TechnologyPanasonicPhonesTelephone
64TechnologySamsungPhones
65TechnologySamsungPhonesCase
66TechnologySamsungPhonesGalaxy
67TechnologySamsungPhonesHeadset
68TechnologySonyAccessoriesMemory Card
69TechnologySonyAccessoriesUSB 2.0 Flash Drive
Table
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Welcome to the MrExcel board!

In your sample, what result(s) would you expect for cell E6:E23 in Main Sheet & why?

For Brand 'Apple' and Sub-Category 'Phones', which all those rows have, sheet 'table' has both "EarPods" and "Mobile" (cells E5:E6) that fit those specifications.
 
Upvote 0
Hello Peter,

I want to extract the "types" by extracting it from the Product Name. The Table Sheet displays the dataset with all the sub-categories. where I am stuck is the brands have common sub-categories; sometimes even common categories. I was actually to match the three conditions Category, Brand and sub-category and then search the types from the Product name and display it in the Main Sheet in Types Column.

For Example,

1. Row 11 - It matches "Technology", "Phones", "Apple" and then from the types in Table Sheet, it searches or finds "Earpods" as the type for that product name and display it F7 on the Main Sheet.

2. Row 123 - It matches "Technology", "Accessories", "Imation" and then from the types in Table Sheet, it searches or finds "Usb 3.0 Flash Drive" as the type for that product name and display it F318 on the Main Sheet.

If it does not find any then it returns with an error

Best Regards,
Brijesh
 
Upvote 0
Sorry, still not clear.

holyvulcan said:
Row 11 - It matches "Technology", "Phones", "Apple" and then from the types in Table Sheet, it searches or finds "Earpods" as the type for that product name and display it F7 on the Main Sheet
Click to expand...
Why Earpods when "Technology", "Phones", "Apple" also matches Mobile in 'table' sheet?

1613126891474.png


holyvulcan said:
Row 123 - It matches "Technology", "Accessories", "Imation" and then from the types in Table Sheet, it searches or finds "Usb 3.0 Flash Drive" as the type
Click to expand...
Why "Usb 3.0 Flash Drive" when "Technology", "Accessories", "Imation" also matches "USB 2.0 Flash Drive" and "USB flash drive" in 'table' sheet?

1613126944067.png
 
Upvote 0
because within the same brand there are multiple types. the items under "Technology, Accessories and Brand" are three types
1. USB Flash Drive
2. USB 2.0 Flash Drive
3. USB 3.0 Flash Drive

If the first three criteria matches i.e. technology, accessories and brand; can it be possible to extract one of the three types from the "Product Name" (in Table 1) and display it in the corresponding column of Types in Table 1
 
Upvote 0
holyvulcan said:
If the first three criteria matches i.e. technology, accessories and brand; can it be possible to extract one of the three types from the "Product Name" (in Table 1) and display it in the corresponding column of Types in Table 1
Click to expand...
OK, so you just want to return any one of the available Types.
Try this in E2 of 'Main Sheet' and copied down.

Excel Formula: 
=INDEX(FILTER(table!E$2:E$69,(table!C$2:C$69=D2)*(table!D$2:D$69=C2)*(table!B$2:B$69=B2),""),1)&""
 
Upvote 0
Thank you for your advise, i did try it but it did not work. It just gives a blank cell. I want the formula to return the type. after it searches the different types within the brand.

for example. in the main sheet on the row 125, the product name is "Imation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P .."
So the formula will first match the three criteria and then search the types - USB Flash Drive, USB 2.0 Flash Drive and USB 3.0 Flash Drive, in the product name "Imation 32GB Pocket Pro USB 3.0 Flash Drive - 32 GB - Black - 1 P .." and then return the correct type i.e. USB 3.0 Flash Drive in the type column.

I have also tried using FILTER and SEARCH function but can not move ahead.
SEARCH(FILTER(Table!$B$2:$E$69,(Table!$C$2:$C$69=E164)*(Table!$D$2:$D$69=D164),""),G164,1)

Best Regards,
Brijesh
 
Upvote 0
Hi Peter,

I managed to get it working; well only the formula got longer. really thank you for your valued support by showing the right direction.

INDEX(FILTER(Table!$E$2:$E$69,(Table!$C$2:$C$69=E40)*(Table!$D$2:$D$69=D40),""),MATCH(MAX(IFERROR(SEARCH(FILTER(Table!$E$2:$E$69,(Table!$C$2:$C$69=E40)*(Table!$D$2:$D$69=D40),""),G40,1),0)),IFERROR(SEARCH(FILTER(Table!$E$2:$E$69,(Table!$C$2:$C$69=E40)*(Table!$D$2:$D$69=D40),""),G40,1),-1),0),1)

Best Regards,
Brijesh
 
Upvote 0
Solution
Glad you got it sorted. Thanks for letting us know. (y)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top