Convaluted Lookup

Topic

New Reply

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.

Reply | Quote

Replies

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. :cheers:

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
  
  zCaller = Application.Caller.Address
  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()

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Alba,

You’re welcome. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!!

Reply | Quote

Alba,

Ok, here’s a version that only uses one column. These assumptions apply:

[*]The date of a rate is the date it STARTS.
[*]The previous rate ends o the date before the next one starts.
[*]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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote