VBA lookup

[delexcel]

Hi,

I have the following code:

=IFERROR(MIN(
    IF(TEXT($A$2:$A$154810,"mmm")=$L40,
    IF($B$2:$B$154810>=N$38,
    IF($B$2:$B$154810<=N$39,
    IF(ISNUMBER($C$2:$C$154810),
    $C$2:$C$154810))))),"")

At present it is searching range $A$2:$A$154810, comparing the month of a cell in the range, to cell L40, which states a month.

If the month is the same, then it is searching range $B$2:$B$154810 to check whether the time of a cell in the range is between two different times (N38 & N39).

If all of those are true, then it returns a number in the range $C$2:$C$154810.

MY QUERY ...

At present, the first query is just looking up the month (mmm) of the date in the range $A$2:$A$154810.

Is it possible to ALSO look up the day (d) of the date in the range? And compare it to a 'day value' (1-31) in cell M40?

I tried modifing:

IF(TEXT($A$2:$A$154810,"mmm")=$L40,

to

IF(AND(TEXT($A$2:$A$154810,"mmm")=$L40,TEXT($A$2:$A$154810,"d")=$M40),

but unfortunately that did not work.

 

[moonfish]

If the value in M40 is numerical you can't compare it with a text value of a number, it won't match. Try using the DAY() function instead of the TEXT() function?

 

[delexcel]

Hi moonfish,

Tried that suggestion but unfortunately that didn't work.

 

[moonfish]

What about something like...

=IF(AND(TEXT($A$2:$A$154810,"mmm")=$L40,TEXT($A$2:$A$154810,"d")=TEXT($M40,"0"))

 

[CharlesChuckieCharles]

TEXT($A$2:$A$154810,"d") is text

Try

VALUE(TEXT($A$2:$A$154810,"d"))=

 

[delexcel]

Thank you to those who have posted replies.

Unfortunately it is still not working.

The following code that is looking up the day is working:

=IFERROR(MIN(
    IF(VALUE(TEXT($A$2:$A$154810,"d"))=$M40,
    IF($B$2:$B$154810>=N$38,
    IF($B$2:$B$154810<=N$39,
    IF(ISNUMBER($C$2:$C$154810),
    $C$2:$C$154810))))),"")

As is the original code that was looking up the month.

However trying to specify that the day AND the month must be the same before it proceeds does not seem to work.

Any suggestions would be greatly appreciated.

 

[delexcel]

Thank you to those who have posted replies.

Unfortunately it is still not working.

The code that is looking up the day is now working:

IF(VALUE(TEXT($A$2:$A$154810,"d"))=$M40,

As is the original code that was looking up the month.

However trying to specify that the day AND the month must be the same before it proceeds does not seem to work.

That being said, I have been successfully able to combine the day and the month in the following code, however the difference is I made the range much shorter - why would this cause the problem?

=IFERROR(MIN(
    IF(AND(VALUE(TEXT(A50:A58,"D"))=$M40,TEXT(A50:A58,"MMM")=TEXT($L40,"0")),
    IF(B50:B58>=G$38,
    IF(B50:B58<=G$39,
    IF(ISNUMBER(C50:C58),
   C50:C58))))),"")

Any suggestions would be greatly appreciated.

 

[delexcel]

I just discovered an interesting point.

=IFERROR(MIN(
    IF(AND(VALUE(TEXT(A50:A58,"D"))=$M40,TEXT(A50:A58,"MMM")=TEXT($L40,"0")),
    IF(B50:B58>=G$38,
    IF(B50:B58<=G$39,
    IF(ISNUMBER(C50:C58),
   C50:C58))))),"")

The above code works because the range A50:A58 only spans one day.

As soon as the range is increased to more than one day the result returned is zero again.

 

[delexcel]

Is anyone able to help?

 

[CharlesChuckieCharles]

What are you attempting to do

find a date/time? or many date/times between a date and a time slot?

what do you want to return if there is more than result one entry for your criteria

?? so to reiterate what are you attempting to do?