Search specific text within a text and return normalized value from mapping table

[k1282]

I have a specific cell in each row which contains text, which needs to be normalised.

Currently, I normalise the text by looking for a specified string with wildcards and return a value which I specify via an IF THEN and CountIF function, that looks like this:
=IF(COUNTIF(A2;("*Ginger Tea*"));"F&B";
IF(COUNTIF(A2;("*Cappuccino*"));"F&B";
IF(COUNTIF(A2;("*Sandwiches*"));"F&B";
IF(COUNTIF(A2;("*Flat White*"));"F&B";
"Other"))))

After a few weeks, it just got too much work, because new strings do popup every week because they get added by colleagues into a tool from which i pull the exports for analysis. So i am basically manually adding the new string into the cell containing the IF THEN statement and updated all cells containing that function.

There must be an easier way to it, isnt there? I was thinking of some kind of mapping table on a seperate sheet where I would add every new string allowing me to have a better overview and to also rename values where necessary. I attached sample data including a new mapping table on a seperate sheet in the hope that you have an idea on how to solve it with a mapping table.

[scottiex]

just write a list of the terms without the * or quotes and try this in C2 and fill down

Please Login or Register  to view this content.

[k1282]

sorry, for the confusion but the category from the mapping table is not just F&B or other. there are 10 different categories in total actually. would this also work somehow?

[scottiex]

Maybe you can use vba a then

you can write
=searchit("A2")

Please Login or Register  to view this content.

[hungt]

sorry, for the confusion but the category from the mapping table is not just F&B or other. there are 10 different categories in total actually. would this also work somehow?

1. Mapping!A2:A4 = *Cappuccino*, *Flat White*, *Sandwiches* (not "*Cappuccino*", ...)

2. Formula C2

Please Login or Register  to view this content.

[k1282]

VBA is not an option for me.

i havent tried hungt suggestion but found the following formula to be working:

=ArrayFormula(INDEX('Mapping'!B:B;MATCH(TRUE;ISNUMBER(SEARCH('Mapping'!A:A;A2));0)))  

what would be the difference between hungt and my formula? Any idea what the associated risks might be?

[scottiex]

as written hungt's formula should be substantially faster.
you could make yours faster by restricting the range on the mapping sheet but I guess it will still be slower as an array formula.

when there are multiple matches your formula give different results (first match vs last match).

[k1282]

hi hungt,

i tried your formula but it is not working as required. It works apparently if the word I am looking for is in the beginning of the string. But it doesnt work, if the word I am looking for is somewhere in the middle of the string. This would explain why it doesnt identify sandwiches in my sample file

[hungt]

Impossible. Give an example.

[k1282]

beats me.

the attached file will show you

[hungt]

beats me.

the attached file will show you

At my place

Attachment 659758

[hungt]

I know why. I gave the excel formula and you have google spreadsheet.

In Google Spreadshet

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[k1282]

the array formula now works.

and the results appear to be more accurate. i need to understand this better:
what does your lookup function actually do as compared to my index/match/search function?

[hungt]

the array formula now works.

and the results appear to be more accurate. i need to understand this better:
what does your lookup function actually do as compared to my index/match/search function?

Comments:

1. The LOOKUP function does not return an error when lookup_vector contains error values ​​and values ​​of different types than lookup_value. E.g. lookup_value = 2 (number) and lookup_vector contains #DIV/0!, #VALUE!, "hichic" then LOOKUP ignores #DIV/0!, #VALUE!, "hichic" (string)

for example.

A1 = 0, A2 = 1 / A1 (#DIV/0!), A3 = "hichic" (string), A4 = 3, A5 = A3 * 1 (#VALUE!), A6 = 5

= LOOKUP(4; A1:A6) returns 3 - A2, A3, A5 are being ignored. A3 is ignored because it contains a string and lookup_value is a number.

= LOOKUP("zzz"; A1:A6) returns "hichic" - A1, A2, A4, A5, A6 are ignored. A1, A4, A6 are ignored because they contain numbers and lookup_value is a string.

2. LOOKUP uses binary search. LOOKUP assumes (but does not check) that lookup_vector is sorted in ascending order.

3. Because LOOKUP uses binary search and assumes that lookup_vector is sorted in ascending order, so if it doesn't find lookup_value, it returns the last position. So if we want to get the last result then we have to make lookup_value certainly greater than all values ​​in lookup_vector.

The LOOKUP(2; 1 / (...) ... construction uses point 3.

In our example, COUNTIF(A3; Mapping! $A$2:$A$4) returns array {0; 1; 0}. 1 / COUNTIF(A2; Mapping!$A$2:$A$4) returns array {#DIV/0!; 1; #DIV/0!}. Because LOOKUP ignores #DIV/0!, So there is only 1. In the array of ones only, LOOKUP will certainly not find 2. LOOKUP will also not find lookup_value if lookup_value is any number greater than 1. In that case it returns the last - the second (in our case there is only one item) item, ie "Flat".

Other examples using point 3.

A2 = 11, B2 = blank, C2 = 2, D2 = hichic, E2 = blala, F2 = 100, G2 = hehe, H2 = 3, I2 = blank

1. We're looking for the last number in A2:I2

Please Login or Register  to view this content.

We must provide lookup_value> all numbers in A2:I2. Then LOOKUP returns the last item of the same type as lookup_value. That's why we choose lookup_value = 10^10.

2. We are looking for the last string in A2: I2

Please Login or Register  to view this content.

We need to provide lookup_value> all strings in A2:I2. Then LOOKUP returns the last item of the same type as lookup_value. That's why we choose lookup_value = "zzz".

3. We have

Attachment 659986

We want Sale for A, "New York", meaning Sale = 500
[Code]
= LOOKUP(2; 1/(A2:A10="A")/(B2:B10="New York"); C2:C10)
[/ Code]

(A2:A10="A") returns an array {TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}

(B2:B10="New York") returns an array {TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE}

1 / (A2:A10="A") / (B2:B10="New York") returns an array (note: 1 / TRUE = 1/1 = 1, 1 / FALSE = 1/0 = #DIV/0!) {1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!}

LOOKUP (ignores #DIV/0!) returns position 5 in C2:C10, i.e. returns C6 = 500.

In the example above, we have 2 conditions - column A = "A", column B = "New York". If we have n conditions then
[Code]
= LOOKUP(2; 1 / (...) / (...) /.../ (...); ...)
[/ Code]
There must be n (...) - n conditions.

Instead of 2, any number greater than 1 can be used.

[k1282]

hungt, you are awesome. thanks for the extensive explanation. helped a lot!