Microsoft

Returning the first and last item in a subset in Excel

Returning the first and last items in a list is easy, but try returning the first and last items in a subset. Now, that's hard work!

Knowing where a list begins and ends can be helpful. For instance, the simple sheet in the figure below tracks time. You might want to use the first and last dates in the list to mark the period beginning and ending dates. Since the dates are in sequential order, it's easy to discern the first date in the list. The last date can take a little more effort if the list is long. You have to browse to the last entry—not such a big deal right?

But let's complicate the situation a bit. Suppose you need to know the first and last elapsed time entry for each date (there's your subset). That changes things quite a bit—is that complicated enough?

The first thing you need is a unique list of dates. To generate this list, use an Advanced Filter as follows:

  1. Select the dates and header cell in column A—that's A1:A8 for this example.
  2. From the Data menu, choose Filter | Advanced Filter.
  3. In the resulting Advanced Filter dialog box, click the Copy To Another Location option. Then, enter G1 in the Copy To control. Be sure to check the Unique Records Only option.
  4. Click OK and Excel will copy a list of unique values to column G.

Now, you need a formula that returns the first item for each date. That formula is simple and you might be familiar with it. I recommend VLOOKUP(), in the following form:

VLOOKUP(lookupvalue,table,columnoffset,exactmatch)

In cell H2, enter the formula

=VLOOKUP(G2,A2:D8,4,FALSE)

In this case, lookupvalue is G2—the value you want to match in table. When the function finds a match, it returns the corresponding value in table's fourth column. The last argument is FALSE, which forces the function to return only an exact match. Copy the formula to cells H3:H5.

Unfortunately, this function isn't flexible enough to also return the last item. In fact, none of the lookup and reference functions can do so (not that I know of).

To find the last item in a subset—the last Elapsed Time value for a specific date—combine HLOOKUP() and MATCH(). The truth is, over the years, I've seen many convoluted formulas for this task. Combining these two functions in the following form is the most efficient solution, that I've come up with:

HLOOKUP(hlookupvalue,htable,MATCH(lookupvalue,table)+1)

Enter the following formula in cell I2 and copy it to cells I3:I5:

=HLOOKUP("Elapsed Time",$D$1:$D$8,MATCH(G2,$A$2:$A$8)+1)

The MATCH() function returns the relative position of the value that matches the lookup value. For example, when matching the value in G2, 6/2/2010, this function returns 3—the relative position within table of the last matching value. The HLOOKUP() function uses the result of the MATCH() function to specify the row in htable, from which it grabs its value, as follows:

HLOOKUP("Elapsed Time",$D$1:$D$8,MATCH(G2,$A$2:$A$8)+1)
HLOOKUP("Elapsed Time",$D$1:$D$8,3+1)
HLOOKUP("Elapsed Time",$D$1:$D$8,4)

As a result, the HLOOKUP() function returns the value in the fourth row of htable (D1:D8)—the last entry for the lookup date of 6/2/2010.

If you have a more efficient method for finding the last item in a subset, please share it!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks