Which lookup should I use?
 

Hi,

I have a spreadsheet that calculates hours worked and wages due based on an
hourly rate. If people work on public holidays then the rate is different. I
have the public holidays for the year in a separate sheet in array B4:C13 (2
years of holidays) in a short date format like 26-Oct, 27-Oct etc. On my main
worksheet I have the date in the same format in cell B3. What I need to do is
if the date in B3 is in the array B4:C13 (in the separate sheet) meaning that
it is a public holiday, then I need to apply a multiplier to the rate - for
example the rate may be doubled or more. Which lookup option should I use for
this?

regards,

Scott

Which lookup should I use?
 

Hi!

B4:C13 (2 years of holidays)

Is B4:B13 for the current year and C4:C13 for next year?

Why not just use a single column and include the year in the date format?

When you use the short date format the year defaults to the current year.

Then, a formula like this:

=IF(ISNUMBER(MATCH(B3,Holiday_Sheet!B4:B23,0)),Rat e*Multiplier,Rate)

Biff

"SB" wrote in message
...
Hi,

I have a spreadsheet that calculates hours worked and wages due based on
an
hourly rate. If people work on public holidays then the rate is different.
I
have the public holidays for the year in a separate sheet in array B4:C13
(2
years of holidays) in a short date format like 26-Oct, 27-Oct etc. On my
main
worksheet I have the date in the same format in cell B3. What I need to do
is
if the date in B3 is in the array B4:C13 (in the separate sheet) meaning
that
it is a public holiday, then I need to apply a multiplier to the rate -
for
example the rate may be doubled or more. Which lookup option should I use
for
this?

regards,

Scott

Which lookup should I use?
 

Hi

At start, redesign the holidays table, so that all holidays are in single
column, p.e. Sheet1!B4:B23;
The formula will be something like (with date in A2)
=Rate*(1+(Multiplier-1)*NOT(ISNA(VLOOKUP(A2,Sheet1!$B$4:$B$23,1,0))))


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"SB" wrote in message
...
Hi,

I have a spreadsheet that calculates hours worked and wages due based on
an
hourly rate. If people work on public holidays then the rate is different.
I
have the public holidays for the year in a separate sheet in array B4:C13
(2
years of holidays) in a short date format like 26-Oct, 27-Oct etc. On my
main
worksheet I have the date in the same format in cell B3. What I need to do
is
if the date in B3 is in the array B4:C13 (in the separate sheet) meaning
that
it is a public holiday, then I need to apply a multiplier to the rate -
for
example the rate may be doubled or more. Which lookup option should I use
for
this?

regards,

Scott

Which lookup should I use?
 

Works a charm, thanks for your help.

"Biff" wrote:

Hi!

B4:C13 (2 years of holidays)

Is B4:B13 for the current year and C4:C13 for next year?

Why not just use a single column and include the year in the date format?

When you use the short date format the year defaults to the current year.

Then, a formula like this:

=IF(ISNUMBER(MATCH(B3,Holiday_Sheet!B4:B23,0)),Rat e*Multiplier,Rate)

Biff

"SB" wrote in message
...
Hi,

I have a spreadsheet that calculates hours worked and wages due based on
an
hourly rate. If people work on public holidays then the rate is different.
I
have the public holidays for the year in a separate sheet in array B4:C13
(2
years of holidays) in a short date format like 26-Oct, 27-Oct etc. On my
main
worksheet I have the date in the same format in cell B3. What I need to do
is
if the date in B3 is in the array B4:C13 (in the separate sheet) meaning
that
it is a public holiday, then I need to apply a multiplier to the rate -
for
example the rate may be doubled or more. Which lookup option should I use
for
this?

regards,

Scott