Nested Index or match formulas?

[scotttynan]

Hi Everyone, new member here.

I have a particularly difficult challenge I have been unable to solve. Hopefully someone here can help.

I have two seperate sets of information

One is the source data and the other is my search criteria.
Basically I need to match the earliest date(prior to any fluctuation in price) where the price matches the search criteria and the cost item matches the search criteria.
I have added an example for cost item 3916 if you look at the data the earliest uninterrupted date where the prices match is May 2016.
Any ideas?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 286"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Source Data[/TD]
[/TR]
[TR]
[TD]Cost item no[/TD]
[TD]Invoiced on Month[/TD]
[TD]unit price[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]August 2015[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]August 2015[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]August 2015[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]August 2015[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]August 2015[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]November 2015[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]November 2015[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]December 2015[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]December 2015[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]January 2016[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]February 2016[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]March 2016[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]March 2016[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]March 2016[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]April 2016[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]April 2016[/TD]
[TD]32.62[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]May 2016[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]May 2016[/TD]
[TD]28.54[/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]May 2016[/TD]
[TD]28.54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 325"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Search Criteria[/TD]
[/TR]
[TR]
[TD]Cost item No.[/TD]
[TD]QUANTITY_1_PRICE[/TD]
[TD]Retruned result[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3839[/TD]
[TD]150.6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3847[/TD]
[TD]25.3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3849[/TD]
[TD]25.3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3855[/TD]
[TD]29.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3856[/TD]
[TD]29.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3864[/TD]
[TD]29.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3866[/TD]
[TD]89.69[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3870[/TD]
[TD]29.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3878[/TD]
[TD]45.24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3882[/TD]
[TD]45.24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3886[/TD]
[TD]35[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3893[/TD]
[TD]55[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3894[/TD]
[TD]24.24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3900[/TD]
[TD]18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3907[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3911[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3916[/TD]
[TD]28.54[/TD]
[TD]May 2016[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD]30[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 675"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance for any thoughts on this.

 

[Max1616]

You can use an array formula:

{=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}

To enter an array formula place this formula in a cell then press control+shift+enter:
=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")

 

[scotttynan]

You can use an array formula:

{=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}

To enter an array formula place this formula in a cell then press control+shift+enter:
=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")

Thanks that is very close to what I am needing, except this formula is returning "November 2015" where as I need it to return the newest date where it first stopped matching the SourceCritera price.

 

[Max1616]

Oh, I misread the original post and thought you wanted the oldest date. If you change the small formula to large it should work:

{=IFERROR(LARGE(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}