I have a data file, where there is a table like this:
(DATA TABLE)
REF_ID | COST | TYPE
======================================================
123_COMPUTER_XYZ | 50000 | (to be filled)
45623_LAPTOP_AOS | 90000
BHJ_WALLET_ASODO | 1000
BUIGQ32_TRIMMER | 200
...
Now, I want to add another column to the table, wherein the type
of the product is automatically put using a lookup table, which I have defined as follows:
(TYPE TABLE)
PRODUCT SUBSTRING | TYPE
=====================================
COMPUTER | computer
LAPTOP | computer
WALLET | personal
TRIMMER | personal-care
...
I want to use the PRODUCT SUBSTRING
column in the TYPE TABLE as a lookup and put the corresponding value of TYPE
into the
3rd column of the DATA TABLE.
I've looked at many questions:
- Find Substring Within Column - Excel
- Lookup and return value based on sub-string match
- http://www.mrexcel.com/forum/excel-questions/8800-substring-search-within-array-strings-part-2-a.html
but all of them ask it the other way round, wherein the string to be searched (REF_ID field) is a sub-string of the TYPE-TABLE.
Can you help me?