I am currently putting together a database of timesheets which contain Name, date, hours (plus others, but these are main ones). I need to lookup into a standing data table two criteria, the name and the date. The standing data consists of name, date of rate change and new rate. Therefore I need to find the person and rate applicable at date of timesheet. I have put something together using index and match but this only works for exact date matches. Any suggestions??
I'm sure there are better ways, but I did something like that recently with a calculated column in the data table. I basically concatenated the two columns I needed matches for into a third column (formula was simple, "=a1&b1"), and did the vlookup (or index/match) on that column.
Omar Freeman Kitchener, ON
Assuming that the rates table is in strict ascending date orde, this should work
=INDEX(rates.table,MATCH(ts.date,IF(INDEX(rates.table,0,1)=name,INDEX(rates.table,0,2)),1),3)
Regards
Bob