MacLochlainns Weblog

While the VLOOKUP and HLOOKUP functions are powerful tools in Excel, they limit their search to the first column or row of a range. This post shows you how to leverage the COUNTIFS function to perform an inclusive between search against the first two columns or rows of a range.

Moreover, the VLOOKUP function searches the first column of a range and looks for an approximate or exact match. The approximate match search throws an exception when the lookup key’s value precedes the first item in the range, and picks up the row immediately less than the lookup key. This type of search requires two condition. The data must be presorted into an ascending order for the sort column, the range must be contiguous, and two matching keys shouldn’t exist. That means that each search column or row cell points to the row of interest for any search key greater than it and less than the next. An exact match search finds the row that matches the lookup key and throws an error when there isn’t an exact match.

Neither of these allow for range searches between non-contiguous sets, like the one below. The date ranges where a value should be found are from the 16th of a month to the end of a month rather than the range between the 16th of one month to the 15th of the next. While this could be done by structuring a row with zeros the gap periods, a more effective solution is possible by using the COUNTIFS. At least, this is true from Excel 2007 forward.

The solution to this problem starts with recognizing how a COUNTIFS works. The COUNTIF provides the opportunity to compare a range of values against a single value, and the COUNTIFS allows multiple comparisons of values against ranges of values. The COUNTIFS function returns the number of matches that meet all conditions. Therefore, when a value is found in only one of the ranges the COUNTIFS function returns a 1, and when a value is found n times it returns n as a number.

The formula in cell F2 checks for the number of times the value in F1 exists:

=COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)

If you evaluate when the foregoing function returns 1 before performing a VLOOKUP function, you can guarantee a match within a non-contiguous range of values. That formula is:

=IF(COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)=1,VLOOKUP(F1,A2:C13,3),0)

Hope this helps some folks, as always …

I had a chance (30 minutes) to play with Excel 2011 on the Mac OS X today. I was shocked to discover that Excel 2011 didn’t appear to support User-Defined Functions (UDFs) like Excel 2010 for Windows. My understanding was that this release would be one where it implemented Visual Basic for Applications (VBA) like Windows. Initially I thought it didn’t but I bought my own copy, did a full install with Visual Basic, and it appears that Microsoft has delivered. Oops, my bad for assuming the machine I played on originally had a solid installation. It appears to have had only a standard installation.

Watch out because UDFs fail with a #NAME! error on a standard install of Excel 2011. While they’re found in the Insert Function dialog in both cases, they only appear to work with a full installation. The downside for Insert Function is that, like Excel 2008, it has no quick poplist to narrow the function choices to groups. We have the big list once more.

Here are my test functions:

Function hello()
  hello = "Hello World!"
End Function
 
Function hellowhom(nameIn As Variant)
  hellowhom = "Hello " + CStr(nameIn) + "!"
End Function

I think I found the trick to get Excel 2011 recognize and run User-Defined Functions. Make sure you do a custom installation and check Visual Basic for Application. Anyway, you can test these functions like that shown below. Column A contains the text of the formulas (a preceding single quote does that), and column B contains actual function calls.

Enabling the Developer ribbon took a few moments when I got my own copy. I figured that updating this was better than making a new post on the blog and linking them. It’s a three step process.

1. Navigate to the Excel menu item and select Preferences…, as shown below.

2. In the Excel Preferences shown below, click the Ribbon icon.

3. In the Show or hide tabs, or drag them into the order you prefer: box shown below, enable the Developer checkbox.

It’s awesome, now accountants and economists can switch to Mac OS X without having to host a virtual machine with Microsoft Excel.

Microsoft Excel has many Visual Basic for Application (VBA) features that are seemingly not well understood. One of these features is passing a range value into a VBA procedure (a Sub) or function. The following example demonstrates how to pass a dynamic range to a local multiple dimensional array, and process the uploaded data in a VBA function.

My sample spreadsheet looks like the screen shot below. The formula call is in cell A11 and the text of the formula is in cell B11.

I left a debug MsgBox() call that demonstrates how you size a range. This shows the range above based on zero-based numbering, which means 5 rows are reported as 4 rows because 0 contains a row, and 3 columns are reported as 2 columns for the same reason. If the dialog looks strange to a Windows user, that’s because it’s one generated on a Mac OS X running Excel 2011. 🙂

I kept this as simple as possible to demonstrate the how to do this. Unfortunately, feedback required adding more extensive comments and making it a bit more bulletproof on concepts. The variable names were chosen to help read the syntax. Two loops are used to demonstrate (a) how you assign the range values to a multidimensional array, and (b) how you read the array values in your VBA code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

Function dynamicArray(lookupValue As String, table As Range)
 
  ' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
  ' Row 1 is the first row and 1,048,576 is the last possible row.
  Dim rowStart As Integer
  Dim rowSize As Integer
 
  ' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
  ' Column A is the first column and XFD is the last possible column.
  Dim columnStart As Integer
  Dim columnSize As Integer
 
  ' Create a dynamic multiple dimension array without any physical size.
  Dim multidimensionArray As Variant
 
  ' Define and declare a local returnValue variable.
  Dim returnValue As Boolean
  returnValue = False
 
  ' Assign the starting row and column values, and the length of values.
  ' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
  ' which means you subtract one from the length.
  rowStart = table.Row
  rowSize = table.Rows.Count - 1
  columnStart = table.Column
  columnSize = table.Columns.Count - 1
 
  ' This demonstrates that the range starts in the row and column, and
  ' the length and width of the multiple dimension array.
  ' ----------------------------------------------------------------------
  ' Insert single quotes for the next two lines to suppress testing the program with variables.
  MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
    "(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
  ' ----------------------------------------------------------------------
 
  ' Redimension the arrays maximum size, rows first, columns second.
  ReDim multidimensionArray(rowSize, columnSize)
 
  ' Read through the range and assign it to a local and dynamically sized array variable.
  ' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
  ' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
  ' easiest solution is to use absolute row an dolumn numbers.
  For i = 0 To rowSize
    For j = 0 To columnSize
      multidimensionArray(i, j) = CStr(Cells(rowStart + i, columnStart + j))
    Next j
  Next i
 
  ' Read through the local variable range and view the content set.
  For i = 0 To rowSize
    For j = 0 To columnSize
      ' Check if the lookupValue has been found and return true.
      If lookupValue = CStr(multidimensionArray(i, j)) Then
        returnValue = True
        Exit For
      End If
    Next j
  Next i
 
  ' Return a Boolean value: true when found and false when not found.
  dynamicArray = returnValue
End Function

You would call it with syntax like that below. The range can be any valid range value in Excel, which is any two cell references separated by a colon.

=dynamicArray("LookupString",A1:G5)

As always, I hope this helps others looking for a way to perform this task natively in Microsoft Excel. As an addendum, I thought the absence of a code example for VB on the MSDN web page was quite interesting.