Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |