an easier way??!!

[sker01]

I am looking for an easy, more streamlined way to search if a time is between to times in different cells, and return a value if it falls into the range.
It is GIS data with thousands of lines that I am trying to interpret for a new project and I feel there must be a way.
I think I could do as below, but I am limited by the number of nested if statements, and i dont want to manually type that many if possible..... is there an easier way?

Thanks

=IF(AND(G2=[HID_QUALITY.xlsx]Sheet1!$F$3,H2,[HID_QUALITY.xlsx]Sheet1!$G$30),[HID_QUALITY.xlsx]Sheet1!$C$3,
IF(AND(G2=[HID_QUALITY.xlsx]Sheet1!$F$3,H2>=[HID_QUALITY.xlsx]Sheet1!$G$3,H2<=[HID_QUALITY.xlsx]Sheet1!$G$4),[HID_QUALITY.xlsx]Sheet1!$C$3,
IF(AND(G2=[HID_QUALITY.xlsx]Sheet1!$F$4,H2>=[HID_QUALITY.xlsx]Sheet1!$G$4,H2<=[HID_QUALITY.xlsx]Sheet1!$G$5),[HID_QUALITY.xlsx]Sheet1!$C$4,
IF(AND(G2=[HID_QUALITY.xlsx]Sheet1!$F$5,H2>=[HID_QUALITY.xlsx]Sheet1!$G$5,H2<=[HID_QUALITY.xlsx]Sheet1!$G$6),[HID_QUALITY.xlsx]Sheet1!$C$5,
IF(AND(G2=[HID_QUALITY.xlsx]Sheet1!$F$6,H2>=[HID_QUALITY.xlsx]Sheet1!$G$6,H2<=[HID_QUALITY.xlsx]Sheet1!$G$7),[HID_QUALITY.xlsx]Sheet1!$C$6,
etc, etc.........

 

[jasonb75]

Could you check the first line, it doesn't match the pattern for the rest ($F$3 in used in 2 rows, comma after H2 instead of >= . Is that a typo, or is there a different requirement for the first test?
If there is a difference, would that apply to the last condition as well? It is often better to accompany a description with an example of your data (see link to XL2BB in my signature at the foot of this post) and the expected results rather than to use a formula to describe what you are doing. Picking formulas apart often leads to misinterpretation.

One method that comes to mind (excluding the first part of your formula for now) is

=FILTER([HID_QUALITY.xlsx]Sheet1!$C$3:$C$10,(G2=[HID_QUALITY.xlsx]Sheet1!$F$3:$F$10)*(H2>=[HID_QUALITY.xlsx]Sheet1!$G$3:$G$10)*(H2<=[HID_QUALITY.xlsx]Sheet1!$G$4:$G$11),"")

I believe that the FILTER function will only work if the data source workbook is open, which, based on your formula, appears to be the case.

If the results are numeric then something like SUMIFS might be a preferable option.