Nested Index or match formulas?

scotttynan

New Member
Joined
Dec 7, 2017
Messages
3
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?

Source Data
Cost item noInvoiced on Monthunit price
3916August 201532.62
3916August 201532.62
3916August 201532.62
3916August 201532.62
3916August 201532.62
3916November 201528.54
3916November 201528.54
3916December 201528.54
3916December 201528.54
3916January 201628.54
3916February 201628.54
3916March 201628.54
3916March 201632.62
3916March 201632.62
3916April 201632.62
3916April 201632.62
3916May 201628.54
3916May 201628.54
3916May 201628.54

<colgroup><col><col><col></colgroup><tbody>
</tbody>
Search Criteria
Cost item No.QUANTITY_1_PRICERetruned result
460
3839150.6
384725.3
384925.3
385529.26
385629.26
386429.26
386689.69
387029.26
387845.24
388245.24
388635
389355
389424.24
390018
39070
39110
391628.54May 2016
392130

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
Thanks in advance for any thoughts on this.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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),"")
 
Upvote 0
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.
 
Upvote 0
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),"")}
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top