Research

It's a scary feeling when you realise that you might not, in fact, be able to help the people who think you're a tech wizard.

A group at the hospital where I do volunteer work is trying to evaluate the usage of their emergency room. They're looking for what they call 'superusers', by which they mean 'people who use the ER more than five times a month'. That part is easy -- they just get a report of everyone who used the ER, sort it, look for the high users. They're actually doing this manually, which strikes me as very odd. I know that there has to be a relatively straightforward way to look at that -- since it's an Excel spreadsheet, I'm thinking some kind of little macro -- but that's not what I'm concerned about.

As part of this evaluation, they want to extract data from another report. This other report is a parallel to the first one, with one record for each visit to the ER, but it carries more specific information - the intensity of the problem (known as the ESI, or Emergency Severity Index) being what they really want to know. They're assuming that an ESI of 1 or 2 is certainly justifiable - these are the heart attacks, severe bleedings, and that sort of this. 3's are 'might be's' -- the person could be sick or injured, or they might not. They ought to be there. The 4's and 5's really didn't need to come - those are the earaches, stubbed fingers, and whatnot.

Here's the problem. They want to run down the list of records in the second list, find everyone who's a 4 or 5, and then find a match for those service incidents on the first list. I thought hey, no problem - a VLOOKUP will handle this. As the have the same indicator on both reports - the person's social security number - it should be easy. And then, hours after I said this, it occurred to me: these are by definition people who come multiple times in a month. Maybe multiple times in a week, or in a day. They're going to have multiple records. Looking for a match on the SSN isn't going to hack it if there are multiple records with that SSN. VLOOKUP will only return the first match.

So I looked. Surely, other people have encountered this? And yes, they have. Their VLOOKUP uses functions that look like this:
=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(C1)),COLUMN(C1))

Ah. Yes. Well....