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]
[TABLE="width: 296"]
<tbody>[TR]
[TD]Mapping Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lookup Value[/TD]
[TD]Tag[/TD]
[/TR]
[TR]
[TD]Depr[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]Deprec[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]Depreciation[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Inc[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Rev[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]
[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]
[TABLE="width: 331"]
<tbody>[TR]
[TD]Description Sheet[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Depreciation is based on straight line[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]This house has depr 20%[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]Revenues are up 14%[/TD]
[TD]Depreciation[/TD]
[/TR]
[TR]
[TD]Inc for the year is $$$$$[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Annual Income is up[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]
[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.