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.
https://steemit.com/steem/@paulag/promoting-steem-at-the-excel-online-summit-how-do-you-think-i-did

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.