Match and index

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Made a blank post yesterday and reposted but I never saw the repost. So here it is agai

Calculation!A1=Dat
Calculation!B1:b24= 1:24 (Hours
Calculation!A25=Date+
Calculation!B25:B48= 1:24 (hours
Et
Data!D12:D29 = list of holiday

In Col C on calculations sheet, I am trying to enter a formula that will look at the date and determine Holiday or not. If I fill in the dates for every cell I can use =NOT(ISERROR(MATCH(A4,DATA!$D$12:$D$29,0))), but I would rather not have the date repeating for each hour of the da

How do I do this
Thanks so muc
Tro
 
Troy,

In A25, try using the formula

=IF(ISERROR(MATCH(A1+1,DATA!$D$12:$D$29,FALSE)),A1+1,IF(ISERROR(MATCH(A1+2,D
ATA!$D$12:$D$29,FALSE)),A1+2,A1+3))

This assumes that you don't have more than two days in a row as a holiday.
This formula can be copied down to A49, etc.

HTH,
Bernie
MS Excel MVP

Troy said:
Made a blank post yesterday and reposted but I never saw the repost. So here it is again

Calculation!A1=Date
Calculation!B1:b24= 1:24 (Hours)
Calculation!A25=Date+1
Calculation!B25:B48= 1:24 (hours)
Etc
Data!D12:D29 = list of holidays

In Col C on calculations sheet, I am trying to enter a formula that will
Click to expand...
look at the date and determine Holiday or not. If I fill in the dates for
every cell I can use =NOT(ISERROR(MATCH(A4,DATA!$D$12:$D$29,0))), but I
would rather not have the date repeating for each hour of the day
 
Troy,

Use this in C1, and copy down as far as needed:

=IF(ISERROR(MATCH(INDIRECT("A" &
INT((ROW()-1)/24)*24+1),DATA!$D$12:$D$29,FALSE)),IF(MOD(ROW(),24)=0,24,MOD(R
OW(),24)),"Holiday")

HTH,
Bernie
MS Excel MVP

Troy said:
Thanks for the repy
This isnt exactly what Im looking for. I need something to go in C1:C24
Click to expand...
that will look at the date in A1 and if the date is in the list of holidays
then "Holiday" is the cell contents. Cell by cell would be something like
C1:C24 = if(NOT(ISERROR(MATCH(A1,DATA!$D$12:$D$29,0))),Holiday," ")
C25:C48=if(NOT(ISERROR(MATCH(A25,DATA!$D$12:$D$29,0))),Holiday," ")
etc.
If A1 is the date and and B1=1 b2=2...B24=24 Then in C13 the formula
Click to expand...
would index up 12 cells and over one to find the date, then compare it to
the list to determine holiday or not?
 
Thanks Berni
This is more like it....but the formula is returning 1-24 rather than Holiday

Tro


----- Bernie Deitrick wrote: ----

Troy

Use this in C1, and copy down as far as needed

=IF(ISERROR(MATCH(INDIRECT("A"
INT((ROW()-1)/24)*24+1),DATA!$D$12:$D$29,FALSE)),IF(MOD(ROW(),24)=0,24,MOD(
OW(),24)),"Holiday"

HTH
Berni
MS Excel MV

Troy said:
Thanks for the rep
This isnt exactly what Im looking for. I need something to go in C1:C2
Click to expand...
that will look at the date in A1 and if the date is in the list of holiday
then "Holiday" is the cell contents. Cell by cell would be something lik
C1:C24 = if(NOT(ISERROR(MATCH(A1,DATA!$D$12:$D$29,0))),Holiday," "
C25:C48=if(NOT(ISERROR(MATCH(A25,DATA!$D$12:$D$29,0))),Holiday," "
etc
If A1 is the date and and B1=1 b2=2...B24=24 Then in C13 the formul
Click to expand...
would index up 12 cells and over one to find the date, then compare it t
the list to determine holiday or not
 
Apparently the 4 is coming from IF(MOD(ROW(),24)=0,24,MOD(ROW(),24). Tryiong to trouble shoot but Im not sure what this part is doin
Thanks!
 
Troy,

Are you sure that the date in A1 is in your holiday list of dates? If it
isn't there, the match returns an error, the iserror is true, and the 1
through 24 values are returned.

Perhaps I misunderstood what is in your range DATA!$D$12:$D$29? I took it
to a list of dates for which you want "Holiday" returned.

HTH,
Bernie
MS Excel MVP

Troy said:
Apparently the 4 is coming from IF(MOD(ROW(),24)=0,24,MOD(ROW(),24).
Click to expand...
Tryiong to trouble shoot but Im not sure what this part is doing
 
Back
Top