XLookup almost has everything I need

7개월 전

XLVBA.gif


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.

image.png

NOTE: grape and pear both have a 1, but for grape, it was entered as a string.

Here are the results:
image.png

Based on the following general equation:

image.png


I hope you find this useful, or that it give you some ideas for a script of your own.

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
STEEMKR.COM IS SPONSORED BY
ADVERTISEMENT
Sort Order:  trending

Thats really impressive. I like how the code can return an array. So I have a big confession, I don't know VBA. I do know DAX and a bit of M, which are new languages used in Excel. but I never learned VBA.

Now following, I know I will learn some neat tricks from you :-)

·

Honestly the best way to learn VBA is to record some macros and review how Excel codes the actions, since VBA is an Object-oriented, event-based language. The way I build Excel subs is somewhat the same everytime; start with the process you want to automate and the end-goal and have VBA conduct the necessary actions to complete that action: "Take this data and put it here", "Count this column or number of rows and return this value".

Learning the procedure conventions and best-practices is the main topic to learn and you will pick up the different objects and events as you get more experience. I've got some great VBA examples on my site if you'd like to check them out.

But I'm the opposite, I'm beginning to learn DAX and M!

·
·

DAX and M rock. I don't know VBA ( and want to find the time to learn it) but I do know DAX and a little M.
M and power query can remove the need for many VBA automation tasks, but it can not do everything VBA can do

·

In that case, it’s time for some basic VBA exercises.