VLOOKUP & INDEX/MATCH Are Both Badly Designed Functions: Here Are Some Better Ones

It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.

So I decided to design and build some better ones.

VLOOKUP INDEX/MATCH problems

Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems

  • Slow exact match (linear search)
  • Approximate sorted match is the wrong default 99.9% of the time and gives the wrong answer without warning
  • Cannot do exact match on sorted data (well they can but only if they ignore sorted!)
  • Numeric VLOOKUP answer column easy to break
  • No built-in error handling for exact match
  • VLOOKUP very inflexible
  • INDEX/MATCH more flexible but still limited
  •  …

MEMLOOKUP/MEMMATCH – easier and faster alternatives to VLOOKUP/MATCH

MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )

The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!

  • Defaults to Exact Match on both unsorted and unsorted data
  • Use either column labels or numbers
  • Fast exact match on both unsorted and sorted data
  • Automatic optimisation of multiple lookups within the same row

So you want more flexibility? Try the AVLOOKUP/AMATCH family of functions

It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.

  • Lookup using any column
  • Lookup using more than one column without slow concatenation
  • Lookup the first, last, Nth or all results on both sorted and unsorted data
  • Lookup both rows and columns (2-dimensional lookup is built-in)
  • Built-in error handling for exact match
  • Return multiple answer columns
  • Case-sensitive lookup option
  • Regex match option

Lookups1

Try them out for yourself

These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.

Download FastExcel V3

If you like them then ask Microsoft to add them to the next version of Excel!

I would be delighted to tell the Excel team how I built these functions and the algorithms they use.

By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.

 

 

Creating an Appointment in Outlook 2010 Installation

Creating an Appointment in Outlook 2010
Creating an Appointment in Outlook 2010 Part II

You can download MGoogleCal.zip

Here are the steps to install the code.

  1. Download the zip file from the link above
  2. Unzip the four files and make a note of where they are
  3. Open Outlook 2010
  4. Press Alt+F11 to open the VBE
  5. Press Ctrl+R to show the Project Explorer

    Yours will no doubt look different than mine, but you should have a project called VbaProject.OTM.

  6. Right click anywhere in that project and choose Import File…
  7. Import the two .bas files and the one .frm file (you have to do them all separately)

  8. Edit (thanks Steffan): Choose Tools – References from the menu and add a reference to Microsoft VBScript Regular Expressions 5.5
  9. Choose Debug – Compile from the VBE menu
  10. Choose File – Save VbaProject.OTM from the VBE menu
  11. Close the VBE
  12. Right click on the Ribbon and choose Customize Quick Access Toolbar
  13. Choose Macros from the Choose commands from: dropdown
  14. Add MakeGoogleAppointment to the Customize Quick Access Toolbar listbox

  15. Press Alt+4 to open the form. My icon is fourth on the QAT so Alt+4 will run the macro. Yours may be in a different spot, so use the number for your situation. If you just press Alt, you’ll see the numbers on the QAT.
  16. Create an appointment

  17. Click OK

  18. And you’re done.

Important: Don’t forget this is mostly untested. Install at your own risk. And let me know what doesn’t work.

Creating an Appointment in Outlook 2010 Part II

Yesterday I posted about replicating how you can enter an item on Google’s calendar in Outlook. Today, we’ll look behind the form to see what’s going on. I won’t cover every piece of code, but you can download the code to see all of it. Let’s start with the Initialize event. In the entry point procedure, I set the When property in certain situations. In the Initialize method, I populate the narrative textbox if When already has a value.

Public Sub Initialize()

If Me.When <> 0 Then
Me.tbxNarrative.Text = Format(Me.When, "h:mm am/pm") & Space(1)
End If

End Sub

If the user has already made some sort of selection on a calendar, this saves having to type it. Changing the narrative textbox also fires an event that is the bulk of the code in the form. Let’s take a look at that now.

Private Sub tbxNarrative_Change()

Dim rxNarrative As VBScript_RegExp_55.RegExp
Dim rxMatches As VBScript_RegExp_55.MatchCollection
Dim dtTimeEntered As Date
Dim sMeridian As String

Set rxNarrative = New VBScript_RegExp_55.RegExp
rxNarrative.Pattern = RegExPattern
rxNarrative.IgnoreCase = True

Me.lbxAppointment.Clear
If rxNarrative.test(Me.tbxNarrative.Text) Then
Set rxMatches = rxNarrative.Execute(Me.tbxNarrative.Text)
With rxMatches.Item(0) 'there's only one match, all the capture groups are submatches of it

'Get AM or PM
sMeridian = GetAMPM(.SubMatches(0), .SubMatches(1))
dtTimeEntered = ConvertStringToTime(.SubMatches(0), sMeridian)

'Account for time zones
Me.When = Me.Day + ConvertTimeToLocal(dtTimeEntered, .SubMatches(2))
Me.What = .SubMatches(3)

'Default to 1 hour duration if not entered
If Len(.SubMatches(5)) > 0 Then
Me.Duration = Val(.SubMatches(5))
Else
Me.Duration = 1 'hour
End If

If Len(.SubMatches(4)) > 0 Then
Me.Location = .SubMatches(4)
End If

End With

Me.tbxNarrative.BackColor = vbWhite
Else
Me.tbxNarrative.BackColor = vbYellow 'visual indicator that narrative doesn't work
End If

'Show the results
UpdateListbox

End Sub

You need to set a reference to the VBScript Regular Expressions 5.5 library. I put the regex pattern in a separate, private function to keep the code tidy. I’ll show that below. If the regex passes, I call Execute to get the matches, then use the Submatches to populate my form’s properties. I get either AM or PM via another function. Then I use that and yet another function to convert the time the user entered into a Date data type. That date gets converted based to the user’s local time. The Duration property is set to 1 hour if the user doesn’t enter a duration. The submatches are in this order

  1. The time, like 3 or 3:00
  2. Either AM, PM, or an empty string if the user didn’t specify
  3. The time zone – EST, EDT, CST, CDT, MST, MDT, PST, PDT, or an empty string if not specified. Definitely could increase these options at some point.
  4. The subject – text that follows the time and precedes either location or duration
  5. The location – text that follows space+at+space
  6. The duration = n.n hour(s) that follows space+for+space

To get AM or PM, I wrote a separate function that takes the time as a string and whatever the user entered for AM or PM if any. If the user entered AM or PM, that’s what’s returned. If not, I assume the time is between 7AM and 6PM.

Private Function GetAMPM(ByVal sTime As String, ByVal sAmpm As String) As String

Dim sReturn As String
Dim dtTime As Date

If Len(sAmpm) > 0 Then
sReturn = sAmpm
Else
dtTime = ConvertStringToTime(sTime, "AM")
If dtTime >= TimeSerial(7, 0, 0) And dtTime < TimeSerial(12, 0, 0) Then sReturn = "AM" Else sReturn = "PM" End If End If GetAMPM = sReturn End Function

This is a little goofy because it checks the time before any time zone shift. I considered applying the time zone shift first, and probably should have, but I didn't want to rewrite the time zone stuff, so I didn't. Maybe I'll fix that in the next version.

The GetAMPM function and the event procedure both use ConvertStringToTime, shown below. It uses the VBA.TimeValue function. TimeValue doesn't work with a single digit, so I append the ":00" if there's not already a colon in there. I also tack on the meridian. When I call it from GetAMPM, I always use AM because I don't know the meridian yet.

Private Function ConvertStringToTime(sTime As String, sMeridian As String) As Date

If InStr(1, sTime, ":") = 0 Then
ConvertStringToTime = TimeValue(sTime & ":00" & Space(1) & sMeridian)
Else
ConvertStringToTime = TimeValue(sTime & Space(1) & sMeridian)
End If

End Function

Once I have the meridian, I recall ConvertStringToTime with the proper value and store that in dtTimeEntered - this is what the user entered converted to a Date data type. The next step is to account for any time zone information entered by the user. I call ConverTimeToLocal, which uses a Windows API to get the users time zone. I got the time zone API from Chip's site.

Public Function ConvertTimeToLocal(ByVal dtTime As Date, ByVal sZone As String) As Date

Dim tzi As TIME_ZONE_INFORMATION
Dim tz As TIME_ZONE
Dim lGmtOff As Long

tz = GetTimeZoneInformation(tzi)

Select Case sZone
Case "EDT"
lGmtOff = -4
Case "EST", "CDT"
lGmtOff = -5
Case "CST", "MDT"
lGmtOff = -6
Case "MST", "PDT"
lGmtOff = -7
Case "PST"
lGmtOff = -8
Case vbNullString
lGmtOff = -tzi.Bias / 60
End Select

ConvertTimeToLocal = dtTime - (TimeSerial(0, tzi.Bias, 0) + TimeSerial(lGmtOff, 0, 0))

End Function

This basically converts the time to GMT, then to the users time. If the user didn't enter a time zone, Case vbNullString, the Bias (in minutes) is added and subtracted leaving the original time.

Everything else in the event procedure is just jamming submatches into properties. I turn the textbox backcolor yellow if the user enters something that the regex can't decipher. At the end of the procedure, all those properties I filled are displayed in a listbox with a call to UpdateListBox.

Private Sub UpdateListbox()

Me.lbxAppointment.AddItem "What: " & Me.What
Me.lbxAppointment.AddItem "Where: " & Me.Location
Me.lbxAppointment.AddItem "Starts at: " & Format(Me.When, "m/d/yyyy hh:mm")
Me.lbxAppointment.AddItem "Ends at: " & Format(Me.EndTime, "m/d/yyyy hh:mm")

End Sub

I created a separate EndTime property that adds the duration to the start time. The duration can be an integer or a decimal and I split that out in a VBA.TimerSerial function. That makes me better than Google.

Public Property Get EndTime() As Date

EndTime = Me.When + TimeSerial(Int(Me.Duration), (Me.Duration - Int(Me.Duration)) * 60, 0)

End Property

As promised, here's the function that holds the regex pattern. I split it out into an array so I could hopefully understand it six months from now.

Private Function RegExPattern() As String

Dim aPattern(1 To 11) As String

aPattern(1) = "^((?:1[0-2]|0?[1-9])(?::[0-5]\d)?)" 'time
aPattern(2) = "\s*" 'optional white space
aPattern(3) = "([ap]m)?" 'optional ampm
aPattern(4) = "\s*"
aPattern(5) = "([ECMP][DS]T)?" 'optional time zone
aPattern(6) = "\s*"
aPattern(7) = "(.*?" 'what
aPattern(8) = "(?=\s+for\s+|\s+at\s+|$))" 'look ahead for ' for ' or ' at '
aPattern(9) = "(?:\s+at\s+(.*?" 'where
aPattern(10) = "(?=\s+for\s+|$)))?" 'look ahead for ' for '
aPattern(11) = "(?:\s+for\s+(\d*(?:\.\d+)?)\s*hour)?" 'duration

RegExPattern = Join(aPattern, vbNullString)

End Function

That's probably enough code for one post. What you don't see, but you can download, is the property getters and setters for UserCancel, Location, When, What, Duration, and Day. There's also code for clicking OK and Cancel, which simply sets the UserCancel property and hides the form.

You can download MGoogleCal.zip

Tomorrow I'll post a couple of notes and some installation instructions if you want to give it a try.

Creating an Appointment in Outlook 2010

I got a new job about a year ago and I went from using Google’s calendar to Outlook. I’ve added some code to Outlook to handle emails a little more like GMail does, but one thing I’ve missed is the ability to add something to the calendar easily. According to Google, you can enter multiple properties of the appointment in one string like “7pm Dinner at Pancho’s” and it’s awesome.

It’s not perfect, though, so I didn’t want to just replicate the function, I wanted to improve it. For one, Google doesn’t deal with fractions of hours very well. Now I can type a narrative in a textbox and create an appointment.

And that opens a pre-filled appointment like this

I started trying to parse the text with a lot of Split() functions, but it quickly became cumbersome. Not impossible, just not very elegant. To be more fancy, I ignored this advice:

Some people, when confronted with a problem, think
“I know, I’ll use regular expressions.” Now they have two problems.

To be perfectly honest, nobody ever confused me with someone who could write regular expressions beyond the incredibly simple ones. But I gave it a go and eventually got some help from stackoverflow and from Rick Measham.

The rules go like this:

  • Start with a time. Can be 5, 5:00, 5pm, 5:00pm, 5 pm, 5 pm CST, 5pmPST and a bunch of other stuff
  • Then the subject or title of the appointment. Stop capturing when you get to " for " or " at " because those are keywords indicating other information.
  • If you get to " at ", everything after that is the location. Stop capturing when you get to " for ".
  • If you get to " for ", everything after that is the duration in hours.

The regex looks like this:
^((?:1[0-2]|0?[1-9])(?::[0-5]\d)?)\s*([ap]m)?\s*([ECMP][DS]T)?\s*(.*?(?=\s+for\s+|\s+at\s+|$))(?:\s+at\s+(.*?(?=\s+for\s+|$)))?(?:\s+for\s+(\d*(?:\.\d+)?)\s*hour)?

Simple, huh? I’ll wrap up this post with a discussion of the entry point procedure. Tomorrow, I’ll discuss the code behind the form.

Public Sub MakeGoogleAppointment()

Dim dtStart As Date
Dim dtDay As Date
Dim ufGoogle As UGoogle
Dim ai As AppointmentItem

'if the user is on a calendar, get the date and/or time
On Error Resume Next
dtDay = Int(Application.ActiveExplorer.CurrentView.SelectedStartTime)
dtStart = Application.ActiveExplorer.CurrentView.SelectedStartTime - dtDay
On Error GoTo 0

'if their not on a calendar, assume today
If dtDay = 0 Then
dtDay = Date
End If

'Get the rest of the string via a form
Set ufGoogle = New UGoogle
ufGoogle.Day = dtDay
ufGoogle.When = dtStart
ufGoogle.Initialize
ufGoogle.Show

'create the new appointment
If Not ufGoogle.UserCancel Then
Set ai = Application.CreateItem(olAppointmentItem)
ai.Start = ufGoogle.When
ai.Duration = ufGoogle.Duration * 60
ai.Subject = ufGoogle.What
ai.Location = ufGoogle.Location
ai.Display
End If

End Sub

The first section attempts to get whatever is selected if the user is looking at a calendar. The SelectedStartTime property returns a Date. It’s only the date portion if the user is on Month view (pretty much the only view I use). It includes both the date and time if the user is on a view that has times. The Int() function gets only the date by lopping off the time if it exists.

If the date is zero, then the user isn’t on a calendar view and I set the date to today.

Next, I instantiate a new userform, pass in some data via Property Let procedures, and run some setup code in an Initialize method.

Finally, if the user doesn’t click Cancel, a new AppointmentItem is created. The duration in my narrative is in hours, but Outlook’s Duration is in minutes, so I multiply by 60 to convert it. The new AppointmentItem is displayed for the user to add more information of change things.