Different solutions exist to work with looked up data in Excel formulas: LOOKUP, VLOOKUP, HLOOKUP.

- LOOKUP(value, lookup_range, result_range): searches for value in the lookup_range and returns the value in the result_range that is in the same position
- VLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
- HLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the top row of table_array and returns the value in the same column based on the index_number.

But I needed yet some other lookup functionality, something like SEARCHHLOOKUP(search_in_text, lookup_range, result_range) where values from the lookup_range are searched in the text of search_in_text and if found the value of the result_range with the same column as were it was found is returned.

I wanted to be able to define some categories, with keywords linked. If a keyword occurs in a sentence, I wanted the category name as result. and I wanted to easily add new keywords for each category, without changing the formula. For example sheet CATEGORIES:

| A | B |

| Fruit | Food |

| 1 | 2 |

1 | apple | chocolate |

2 | banana | milk |

Next I have a cell with value: "Apple belongs to category" In another cell I want a formula (no VBS) that would result in the category name: "Fruit". For example sheet EXAMPLE:

| A | B |

1 | Apple belongs to category | Fruit |

2 | Chocolate belongs to category | Food |

(Example SearchLookup.xlsm)

Since I need to search for the keyword in a sentence, I use the SEARCH(search_text, search_in_text, start_position ) function. If the result is bigger than 0, the keyword was found (not case sensitive). But if the keyword was not found, an error value #VALUE will be returned. To catch this error value, the function IFERROR(value, value_if_error) can be used. So I get this function: {=IFERROR(IF(SEARCH(CATEGORIES!$A$2:$A$5;EXAMPLE!$A1)>0;CATEGORIES!A$1);"")}

But this will only lookup keywords in my first column of Categories, while I want many more. I solved this by attaching a weight to each category and using the formula CHOOSE(position, value1, value2, ... value_n ) to select the category name corresponding it's weight. To ignore the error values when the keyword is not found, I return 0 if an error occurs and the category weight when the keyword was found, so a MAX on that array will result in the matching category weight. To make sure that 0 is returned, only when the keyword is not found, I add any single character (µ in this case) in front of the text to search in, else 0 could be returned if the text to search in starts with the text we are searching. Now the search has to be bigger than 1 when the keyword is found.

The result is this formula: {=CHOOSE(MAX(IFERROR(IF(SEARCH(Categories!$A$3:$A$6;"µ"&Example!$A1)>1;Categories!$A$2;0);0);IFERROR(IF(SEARCH(Categories!$B$3:$B$6;"µ"&Example!$A1)>1;Categories!$B$2;0);0))+1;"";Categories!$A$1;Categories!$B$1)}

Since we apply the SEARCH function on an array, the complete formula has to be an array formula, so don't forget to press CTRL+SHIFT+ENTER to save as an array function and get the "{=...}" signs around the formula.

So using a combination of CHOOSE, MAX, IFERROR, IF and SEARCH functions I can lookup category names base on keywords and the keywords can be added dynamically. The only "problem" left is that I need to change my formulas when a new category is added, but at least not when adding keywords in a category.

To solve this last problem, I ended up creating a 'User defined function' SEARCHHLOOKUP(search_in_text, lookup_range, result_range, (result_range_index)):

Function SearchHLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)

'''''''''''''''''''''''''''''''''''''''

'Written by myT - http://myTselection.blogspot.com

'Values from the lookup_range are searched in the text of search_in_text

'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned

'Example:

'A B

'1 2

'3 4

'if 2 or 4 is found in Search_in_text, B will be returned

'if 1 or 3 is found in Search_in_text, A will be returned

'if none is found, empty string will be returned

'''''''''''''''''''''''''''''''''''''''

Dim iRow, startRow As Integer

Dim iColumn, startColumn As Integer

If Result_range Is Nothing Then

startRow = 2

Else

startRow = 1

End If

startColumn = 1

For iColumn = startColumn To Lookup_range.Columns.Count

For iRow = startRow To Lookup_range.Rows.Count

If Not (Lookup_range(iRow, iColumn) = "") Then

If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then

If Result_range Is Nothing Then

SearchHLookup = Lookup_range(1, iColumn)

ElseIf Not (Result_range_index = 0) Then

SearchHLookup = Result_range(Result_range_index, iColumn)

Else

SearchHLookup = Result_range(1, iColumn)

End If

Exit Function

End If

End If

Next iRow

Next iColumn

SearchHLookup = ""

End Function

So in my example, the function I use now has been simplified to:

=SearchHLookup(A1;Categories!$A$3:$B$4;Categories!$A$1:$B$1;1)

Of course, a SearchVLookup could be made easily as well:

Function SearchVLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)

'''''''''''''''''''''''''''''''''''''''

'Written by myT - http://myTselection.blogspot.com

'Values from the lookup_range are searched in the text of search_in_text

'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned

'Example:

'A 1 2

'B 3 4

'if 1 or 2 is found in Search_in_text, A will be returned

'if 3 or 4 is found in Search_in_text, B will be returned

'if none is found, empty string will be returned

'''''''''''''''''''''''''''''''''''''''

Dim iRow, startRow As Integer

Dim iColumn, startColumn As Integer

If Result_range Is Nothing Then

startColumn = 2

Else

startColumn = 1

End If

startRow = 1

For iRow = startRow To Lookup_range.Rows.Count

For iColumn = startColumn To Lookup_range.Columns.Count

If Not (Lookup_range(iRow, iColumn) = "") Then

If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then

If Result_range Is Nothing Then

SearchVLookup = Lookup_range(iRow, 1)

ElseIf Not (Result_range_index = 0) Then

SearchVLookup = Result_range(iRow, Result_range_index)

Else

SearchVLookup = Result_range(iRow, 1)

End If

Exit Function

End If

End If

Next iColumn

Next iRow

SearchVLookup = ""

End Function

The user defined function needs to be defined in a module. Example: SearchLookup.xlsm)