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.

5 comments
fledis
fledis

In case table A1:E8 is sorted by dates(column A) I'd use VLOOKUP in this way: =VLOOKUP(G2+0.1;$A$1:$E$8;4;TRUE) Instead of 4 there can be any another column number. The idea is to make lookup value (date) a bit bigger than original value while a bit smaller than next possible value.

peate
peate

The VLOOKUP, HLOOKUP method is okay, but how about this formula that I came up with to calculate the Subtotal by Date values? I defined two dynamically named ranges, Table and Dates using the OFFSET() function so I don't have to change the formula when data is added, then used INDIRECT and 2 forms of MATCH() to find the subranges for a SUM(). =IF(ROW()=MATCH($A2,Dates,1)+1,SUM(INDIRECT("D"&MATCH($A2,Dates,0)+1):INDIRECT("D"&MATCH($A2,Dates,1)+1)),"")

biaogewang
biaogewang

I use the combination of MAX array with INDEX. This one with HLOOKUP and MATCH is shorter, but only works if you are sure that the lookup value is in the list, and sorted. Or else, it might return a value when it should report an error.

jbenton
jbenton

An alternative method would be to use MAX(ROW(range)*(range=criteria)) to get the row number of required entry and use this in an INDIRECT function, entered as an array with ctrl-sh-enter eg =INDIRECT("d"&MAX(ROW($A$2:$A$8)*($A$2:$A$8=G2))) entered as an array should work but beware changing the layout of your spreadsheet afterwards

jbenton
jbenton

I'd use INDEX or OFFSET instead of HLOOKUP but otherwise pretty much the same FALSE is the default option for the lookup functions, so ...,) instead of ...,FALSE) has the same effect

Editor's Picks