I have some output from a database listing chargeout rates for various
contractors. As these are updated yearly, there are various values for each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the chargeout rate
was on a given date. The thing is, a normal lookup needs exact values, and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

The last argument in VLOOKUP (and HLOOKUP) can be used to get 'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for various
contractors. As these are updated yearly, there are various values for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the chargeout
rate
was on a given date. The thing is, a normal lookup needs exact values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

Ok, so I have effectively 4 columns that I need to reference:

Contractor Number : Week Number : Year : Charge Rate

(The company runs on week numbers rather than dates, but that is continuous
across the data)

The Week / Year is when the Charge Rate was updated to that new figure.
What I need to be able to do is to take timesheet entry with a Week / Year
reference and return the correct Charge Rate.

My problem is that there isn't a charge rate for every week listed, and I
need to make sure that it goes to the right Change entry, rather than to the
next one (for example).

"Bernard Liengme" wrote:

The last argument in VLOOKUP (and HLOOKUP) can be used to get 'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for various
contractors. As these are updated yearly, there are various values for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the chargeout
rate
was on a given date. The thing is, a normal lookup needs exact values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

I will assume that this data
Contractor Number : Week Number : Year : Charge Rate
is on Sheet2 in rows 1 to 500 (row 1 having labels)
I am assuming that each row is unique: not two rows have exactly the same
info

On Sheet1:
A1: has the contract to lookup
B1 has the week-no
C1 and the year


=SUMPRODUCT(--(Sheet2!A2:A500=A1),--(Sheet2!B2:B500=B1),(--(Sheet2!C2:C500=C1),Sheet2!D2:D500)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Ok, so I have effectively 4 columns that I need to reference:

Contractor Number : Week Number : Year : Charge Rate

(The company runs on week numbers rather than dates, but that is
continuous
across the data)

The Week / Year is when the Charge Rate was updated to that new figure.
What I need to be able to do is to take timesheet entry with a Week / Year
reference and return the correct Charge Rate.

My problem is that there isn't a charge rate for every week listed, and I
need to make sure that it goes to the right Change entry, rather than to
the
next one (for example).

"Bernard Liengme" wrote:

The last argument in VLOOKUP (and HLOOKUP) can be used to get 'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for various
contractors. As these are updated yearly, there are various values for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the chargeout
rate
was on a given date. The thing is, a normal lookup needs exact values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

Hi Bernard, thank you for your help.

Would I be correct in assuming that this requires an exact entry for the
date in the timesheet?

"Bernard Liengme" wrote:

I will assume that this data
Contractor Number : Week Number : Year : Charge Rate
is on Sheet2 in rows 1 to 500 (row 1 having labels)
I am assuming that each row is unique: not two rows have exactly the same
info

On Sheet1:
A1: has the contract to lookup
B1 has the week-no
C1 and the year


=SUMPRODUCT(--(Sheet2!A2:A500=A1),--(Sheet2!B2:B500=B1),(--(Sheet2!C2:C500=C1),Sheet2!D2:D500)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Ok, so I have effectively 4 columns that I need to reference:

Contractor Number : Week Number : Year : Charge Rate

(The company runs on week numbers rather than dates, but that is
continuous
across the data)

The Week / Year is when the Charge Rate was updated to that new figure.
What I need to be able to do is to take timesheet entry with a Week / Year
reference and return the correct Charge Rate.

My problem is that there isn't a charge rate for every week listed, and I
need to make sure that it goes to the right Change entry, rather than to
the
next one (for example).

"Bernard Liengme" wrote:

The last argument in VLOOKUP (and HLOOKUP) can be used to get 'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for various
contractors. As these are updated yearly, there are various values for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the chargeout
rate
was on a given date. The thing is, a normal lookup needs exact values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

yes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Hi Bernard, thank you for your help.

Would I be correct in assuming that this requires an exact entry for the
date in the timesheet?

"Bernard Liengme" wrote:

I will assume that this data
Contractor Number : Week Number : Year : Charge Rate
is on Sheet2 in rows 1 to 500 (row 1 having labels)
I am assuming that each row is unique: not two rows have exactly the same
info

On Sheet1:
A1: has the contract to lookup
B1 has the week-no
C1 and the year


=SUMPRODUCT(--(Sheet2!A2:A500=A1),--(Sheet2!B2:B500=B1),(--(Sheet2!C2:C500=C1),Sheet2!D2:D500)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Ok, so I have effectively 4 columns that I need to reference:

Contractor Number : Week Number : Year : Charge Rate

(The company runs on week numbers rather than dates, but that is
continuous
across the data)

The Week / Year is when the Charge Rate was updated to that new figure.
What I need to be able to do is to take timesheet entry with a Week /
Year
reference and return the correct Charge Rate.

My problem is that there isn't a charge rate for every week listed, and
I
need to make sure that it goes to the right Change entry, rather than
to
the
next one (for example).

"Bernard Liengme" wrote:

The last argument in VLOOKUP (and HLOOKUP) can be used to get
'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for
various
contractors. As these are updated yearly, there are various values
for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the
chargeout
rate
was on a given date. The thing is, a normal lookup needs exact
values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?

Ah right. My problem is that not every week has a date, as the database only
lists the changes to charge rates. What I need is to be able to return the
last entered before the date to look up.

"Bernard Liengme" wrote:

yes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Hi Bernard, thank you for your help.

Would I be correct in assuming that this requires an exact entry for the
date in the timesheet?

"Bernard Liengme" wrote:

I will assume that this data
Contractor Number : Week Number : Year : Charge Rate
is on Sheet2 in rows 1 to 500 (row 1 having labels)
I am assuming that each row is unique: not two rows have exactly the same
info

On Sheet1:
A1: has the contract to lookup
B1 has the week-no
C1 and the year


=SUMPRODUCT(--(Sheet2!A2:A500=A1),--(Sheet2!B2:B500=B1),(--(Sheet2!C2:C500=C1),Sheet2!D2:D500)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
Ok, so I have effectively 4 columns that I need to reference:

Contractor Number : Week Number : Year : Charge Rate

(The company runs on week numbers rather than dates, but that is
continuous
across the data)

The Week / Year is when the Charge Rate was updated to that new figure.
What I need to be able to do is to take timesheet entry with a Week /
Year
reference and return the correct Charge Rate.

My problem is that there isn't a charge rate for every week listed, and
I
need to make sure that it goes to the right Change entry, rather than
to
the
next one (for example).

"Bernard Liengme" wrote:

The last argument in VLOOKUP (and HLOOKUP) can be used to get
'nearest'
match rather than 'exact' match.
Give us some idea of the data layout and someone will show you how.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"The Cherub" wrote in message
...
I have some output from a database listing chargeout rates for
various
contractors. As these are updated yearly, there are various values
for
each
contractor, and each change has the date it was instigated.

I need to be able to reference this data to find whatever the
chargeout
rate
was on a given date. The thing is, a normal lookup needs exact
values,
and I
cannot work out how to get it to return the value on a given date.

Any suggestions?