Excel lookups in formulas

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)