Wednesday, March 14, 2007

So what DOES it mean, anyway?

{=IF(ISERROR(INDEX(DownRoute,SMALL(IF(DM=B$33,ROW(DM)-ROW('Route Information'!$B$25)+1,ROW('Route Information'!$B$37)+1),$A36),4)), "",(INDEX(DownRoute,SMALL(IF(DM=B$33,ROW(DM)-ROW('Route Information'!$B$25)+1,ROW('Route Information'!$B$37)+1),$A36),4)))}

Since I’ve had a few requests as to what this formula means or does, I thought I’d try and explain. In a nutshell (where did THAT phrase come from anyway?), this formula looks at one set of data in a spreadsheet, matches that data to another set, and pulls a certain occurrence of that value. For example, in this case, it looks for a District Manager’s name in a range of cells (“DownRoute”) and then returns the occurrence of that name that is set in cell $A34. If $A34 says “2”, it looks for the second occurrence of the name and then returns the value in column 4 of the range. The formula performs similarly to a VLOOKUP formula, with the exception that a VLOOKUP formula can only return the first occurrence of the requested value. The end result of this particular usage is that it returns the start and end dates for when a particular district manager is on route so as to track the total number of days he or she is on route. Cool, huh?

No comments: