INDEX/MATCH and VLOOKUP based on criteria

[Yvonne Love]

I'll do my best to be concise.

I track hours, billings, and receipts for our attorneys/paralegals, and they are tracked not only by person, but level (Shareholder, Associate, etc.) and location (we have 6 offices). Sometimes people will change level or location which results in the need for some kind of lookup based on date.

Ideally, what I want is to be able to lookup up the timekeeper from a table with a date, and have it pull the location and level based on that date. However, I don't want to have a list of every variation as that can be tedious to maintain. I generally use INDEX+MATCH for lookups, but VLOOKUP using the range_lookup as TRUE eliminates the need to have a table with every monthly option. So I would like to incorporate this into my formula so I don't have to do a series of IF THEN formulas to pull the location and level.

I am not sure how to incorporate that logic, if it is possible, to pull the info I want.

I've attached a test spreadsheet. I am looking for as concise a formula as I can use (either VLOOKUP or INDEX+MATCH), without resorting to IF THEN, to pull the respective location and level based on timekeeper and date.

I'm confident in my abilities to make a complex formula to do this, but the more concise the better, and I'm sure there are formulas and combinations of formulas I don't know yet that would be more concise. So I'm consulting the experts!

YL

 

[SirJB7]

Hi, Yvonne Love!

At C column try this:
{=INDICE(Lookup[Location];COINCIDIR([@Timekeeper]&"_"&[@Date];Lookup[Timekeeper]&"_"&Lookup[Date];1))} -----> in English: {=INDEX(Lookup[Location],MATCH([@Timekeeper]&"_"&[@Date],Lookup[Timekeeper]&"_"&Lookup[Date],1))}
For column D change Location by Level.

It's an array formula (indicated by opening & closing braces automatically added by Excel) and it should be entered without braces and pressing Ctrl-Shift-Enter simultaneously instead of just Enter as non-array usual formulas.

Regards!

 

[Yvonne Love]

Thank you!! I'm discovering it only works if the lookup table is sorted by date first, then timekeeper. So if in the example I sent, the 3/15/17 date was in a higher row than the 1/15/17 date for timekeeper ABC, it won't pull correctly. I've played around with it to make sure my existing formula (which wasn't going to work much longer) pulled the same results, and so far it is. This is a great solution so long as the master table is sorted correctly.

Thank you!!
Yvonne

 

[SirJB7]

Hi, Yvonne Love!

Match function with exact value (0 as last parameter) can work on unsorted ranges, but larger/smaller searches require ordered data.
Otherwise you'd have to move to a UDF in VBA which should walk thru the whole range to perform the comparison.

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!