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)
Posts
