1. ## Convaluted Lookup

Hi,

I'm looking for some help regarding a rather convaluted lookup - I have a list of people, that during different time periods had different charge out rates. I now have to find out the rates for particular dates...

The attached example may help make a bit more sense of this issue....I have a table of data that defines each time period and rate for each person, and I need to be able to calculate out what rate each person has on a specific date. Of course this is an over simplified example, so whatever the approach has to be readily scalable!

Any suggestions on how to approach this? Thanks in anticipation

Alba.

2. Alba,

Here's a UDF {User Defined Function} that will accomplish the task. This is a quick first take and it can probablly be tweaked for greater efficiency.

Code:
```Option Explicit

Function iRate() As Integer

'Uses the following Range Names:
'  RateTable  defined as the entire table EXCLUDING the header row.
'  UpperRight defined as the 1st person in the RateTable

'Calling Sequence: =iRate()
Dim iCntr   As Integer
Dim iTblLen As Integer
Dim zCaller As String

iTblLen = Range("RateTable").Rows.Count - 1

For iCntr = 0 To iTblLen

If Range(zCaller).Offset(0, -2).Value = Range("UpperRight").Offset(iCntr, 0).Value Then

If Range(zCaller).Offset(0, -1).Value >= Range("UpperRight").Offset(iCntr, 1).Value And _
Range(zCaller).Offset(0, -1).Value <= Range("UpperRight").Offset(iCntr, 2).Value Then
iRate = Range("UpperRight").Offset(iCntr, 3).Value
Exit Function
End If

End If

iRate = 0   'No match found

Next iCntr

End Function    'iRate()```

3. Very neat indeed! I should be able to scale this up nicely to work with the data I have - thanks very much for your help with this, I would never have got there without your help!

Alba

4. Alba,

You're welcome.

5. Hi,

I'm just working through how to modify this to suit my data - is there a way that I can do the date range lookup based upon the date in the Start column only - ie perform the comparison between the cells vertically with the assumption that the last date for each person has the end date of present day?

??

Alba

6. Alba,

I'm not exactly sure what you have in mind. One thing you could do is to set the last entry's End date for the individual to =Now() and it will work but if that's not what your after please post an example of your lists.

7. I'm not able to post an example just now, but what I mean is the removal of the 'end date' column. So the end date for a rate would become the cell immediately underneath. I'll pop an example up in a couple of hours..Thanks!!

8. Alba,

Ok, here's a version that only uses one column. These assumptions apply:
1. The date of a rate is the date it STARTS.
2. The previous rate ends o the date before the next one starts.
3. The last entry for each person still used the =Now() as the Date and leaves the rate blank. You can enter the next rate by inserting above the line with the =Now() entry and everything will be preserved, e.g. the Range Names.

It is possible to eliminate the =Now() entry but then the logic for the UDF becomes much more involved.

9. Thanks very much Geek! The data does not contain a now() entry, but with a wee bit of work, it can. You are an absolute STAR!!

Alba

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•