# 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
• Case-sensitive lookup option
• Regex match option

### Try them out for yourself

These functions are included in the 90 or so additional Excel functions built into 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

Here are the steps to install the code.

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

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.

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.