XLookup almost has everything I need
Before you read any further, you should read @paulag 's post on the Excel Online Summit. Then take a little time to watch day 1's presentations.
Especially her presentation. Once she starts to merge blockchain with Excel Learning, the host's mind is blown.
Back in around 2013, I was frustrated with vlookup function. It was very close to what I wanted, but there were a couple things that bothered me about the function. I was fluent in the Match/Index/Offset functions and their method of duplicating the capabilities of vlookup, but I wanted to share this with folks at work who weren't as comfortable.
As the folks in the Excel Online Summit pointed out, always try to use the built in functions first before you break into the VBA.
I tried to keep the "look and feel" of the function similar to vlookup, so it would be reasonably simple for folks to switch between the two.
These are the parameters that I included in the formula:
- the value you are searching for
- the range you are searching in
- the column within the range that you want to search (does not need to be the leftmost column)
- the column within the range that you want to return
- do you want to treat numbers and strings the same (so 1 equals "1")
- if no match is found, what would you like to return
- do you want to search for more than one match
- if there are more than one match, what will delimit the results (before dynamic arrays ;-) )
I apologize in advance for the lack of comments in the code.
Function vlookup2(lookup_value As Variant, table_array As Range, column_to_search As Integer, column_to_return As Integer, _ Optional treat_numbers_as_string As Boolean = False, _ Optional result_if_not_found As String = "Not Found", _ Optional search_for_multiples As Boolean = False, _ Optional delim_if_multiples As String = "||") As String Dim rTemp As Range, bFound As Boolean, sResult As String, sSearchString As String Dim iFoundCount As Integer bFound = False iFoundCount = 0 sResult = "" If treat_numbers_as_string Then For Each rTemp In table_array.Rows If CStr(rTemp.Cells(1, column_to_search).Value) = CStr(lookup_value) Then If Not search_for_multiples Then sResult = rTemp.Cells(1, column_to_return).Value bFound = True Exit For Else sResult = sResult & delim_if_multiples & rTemp.Cells(1, column_to_return).Value bFound = True iFoundCount = iFoundCount + 1 End If End If Next rTemp Else For Each rTemp In table_array.Rows If rTemp.Cells(1, column_to_search).Value = lookup_value Then If Not search_for_multiples Then sResult = rTemp.Cells(1, column_to_return).Value bFound = True Exit For Else sResult = sResult & delim_if_multiples & rTemp.Cells(1, column_to_return).Value bFound = True iFoundCount = iFoundCount + 1 End If End If Next rTemp End If If bFound Then Select Case iFoundCount Case 0 vlookup2 = sResult Case 1 vlookup2 = Mid(sResult, 2) Case Else vlookup2 = CStr(iFoundCount) & " found" & sResult End Select Else vlookup2 = result_if_not_found End If End Function
For an example, here is some dummy data.
NOTE: grape and pear both have a 1, but for grape, it was entered as a string.
Here are the results:
Based on the following general equation:
I hope you find this useful, or that it give you some ideas for a script of your own.