Using Search and Vlookup or Index to tag rows using mapping tables

[djstefko]

I have a large data set with a 'description' column that is not always uniform, for example it could read "bla bla bla depreciation" or "depreciation bla bla bla" and both should be tagged as 'Depreciation' in a separate tagging column.

Is there a way/formula which I could create a mapping table with Column A being *depr* and Column B being "Depreciation" that would tag both above examples as 'Depreciation'??
Similar to a vlookup, but the lookup_value wouldn’t be define, instead would be all the possibilities in the mapping table's column A and if found in description would return column b value.

Appreciate any thoughts

 

[Jaggy]

If in column A, you type in what you're looking for:
Depr
Deprec
Depreciation

And assume your descriptions are in column E
bla bla Depr bla bla

You can try this formula
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$4,E2)))>0,"Depreciation","")

you can remove the IF aspects if you just want the formula to return TRUE or FALSE
=SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$4,E2)))>0

 

[djstefko]

Thanks for the response, but I don't think this will solve for what I am trying to do.

I have a description column on one tab and would like to put a tag in the column next to it that is driven by a mapping table on another tab.

My issue is the lookup value from the description column would need search the description for the lookup values in the mapping table and if positive return the second column ('tag') from the mapping table.

 

[Jaggy]

apologies if the formatting doesn't work... i'm not very good at this.
you have your mapping table on one tab:
[et]

Mapping Table
Lookup Value	Tag
Depr	Depreciation
Deprec	Depreciation
Depreciation	Depreciation
Income	Revenue
Inc	Revenue
Rev	Revenue
Revenue	Revenue

<tbody>
</tbody>

[et/]

on your data tab, you have your description column and you want your tag from the table above to show up under the formula column
[et]

Description Sheet
Description	Formula
Depreciation is based on straight line	Depreciation
This house has depr 20%	Depreciation
Revenues are up 14%	Depreciation
Inc for the year is $$$$$	Revenue
Annual Income is up	Revenue

<tbody>
</tbody>

[et/]

I did this with this formula:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH('Mapping Table'!A3:A9,A3)))>0,OFFSET('Mapping Table'!$A$3:$A$9,0,1),"")

 

[Jaggy]

Oh wait.. ignore this.. Just tested it again and it doesn't work after all. The example i used was just a coincidence.