0

I am trying to lookup values in Range K2:AY2 in Column B to return values in Column C and D. If the timestamp in Col J falls between the values in Col C and D, then populate cell as 1, else 0. These 1's and 0's will be populated for each heater in Range K2:AY2.

I can easily do this with an INDEX/MATCH formula. As you can see in the code block below, I was able to generate 1's and 0's for each heater ID. However, only the start and end times of the first lookup instance were utilized to generate the 1's. Heater ID's that were repeated more than once in Col B were not captured in the formula.

I am seeking support from this community to figure out a way to generate 1's for all instances of the Heater ID's found in Col B. Excel logic is great, but I am open to VBA solution as well.

Code in Cell K3

=IF(AND($J3 > INDEX($B$3:$D$66, MATCH(K$2, $B$3:$B$66,0),2), $J3 <INDEX($B$3:$D$66, MATCH(K$2, $B$3:$B$66,0),3)), 1,0)

enter image description here Image 2.0

eclecticengineer
  • 1
  • 1

1 Answers1

0

Perhaps COUNTIFS would work better in your scenario

In Cell K3:

=COUNTIFS($B:$B,K$2,$C:$C,"<="&$J3,$D:$D,">="&$J3)
  • Look up HeaterID in column B from cell K$2
  • Check the date & time is less than or equal to $J3 in column $C:$C
  • Check the date & time is greater than or equal to $J3 in column $D:$D
Kairu
  • 381
  • 1
  • 9