Index Match with multiple criteria including between range

[BradRichardson]

Hi all,

I'm having difficulty with an index match formula.

I'm trying to cross reference two reports, below:



I'd like to use an index match formula (or something else with the same result) to search for any result in the report on the right that meets the following three criteria:

• PIN in column F matches PIN in column A
• Break start time in column H is greater than task start time in column B
• Break end time in column I is less than task end time in column C

IF there is a match, I want to report the corresponding value found in column G into cell D1. I then want to drag this formula down column D to repeat.

Because of the nature of data that I'm reporting, there will only ever be one match per line that satisfies all three criteria in the left report, but many times there will be no match at all.

I've reviewed plenty of forum entries on INDEX MATCH with multiple criteria but can't find one that works for this specific instance.

Thanks!

 

[NARAYANK991]

Hi ,

Attaching a workbook with data , instead of a screenshot , is guaranteed to get you a solution much faster.

Narayan

 

[rahulshewale1]

hiii,

is this you looking for ?


Thanks
Rahul shewale

 

[bosco_yip]

Another option.

In D2, formula copy down :

=IFERROR(1/(1/SUMIFS(G:G,F:F,A2,H:H,">="&B2,I:I,"<="&C2)),"")

Regards
Bosco