In simple terms, I am trying to do this: If TEXT in Sheet3!A2 equal TEXT in Sheet2!C:C and if TIME in Sheet3!D2 is within TIMES in Sheet2!I:I and Sheet2!J:J return TEXT in Sheet2!O:O I was able to build a formula that returns the TEXT in Sheet2!O:O however the time has to be exact and I am only referencing Sheet2!I:I {=INDEX(Sheet2!O:O,MATCH(Sheet3!A2&Sheet3!D2,Sheet2!C:C&Sheet2!I:I, 0))} Is there way to either: (1) allow for a estimation in the time and utilize the formula referenced above (2) reference two criteria (text & time between to values) and using the formula to work over an array =Sheet3!D2=MEDIAN(Sheet3!D2,Sheet2!I:J)

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Referencing time within two times with an additional Text Criteria

Thread starter robersonmr
Start date Jan 4, 2017
Tags

index match median time analysis vlookup + index + match

robersonmr

New Member

Jan 4, 2017

In simple terms, I am trying to do this:
If TEXT in Sheet3!A2 equal TEXT in Sheet2!C:C and if TIME in Sheet3!D2 is within TIMES in Sheet2!I:I and Sheet2!J:J return TEXT in Sheet2!O:O

I was able to build a formula that returns the TEXT in Sheet2!O:O however the time has to be exact and I am only referencing Sheet2!I:I

Code:

{=INDEX(Sheet2!O:O,MATCH(Sheet3!A2&Sheet3!D2,Sheet2!C:C&Sheet2!I:I, 0))}

Is there way to either:
(1) allow for a estimation in the time and utilize the formula referenced above
(2) reference two criteria (text & time between to values) and using the formula to work over an array

Code: