Nested Index or match formulas?

Thanks:  0

# Thread: Nested Index or match formulas?

1. ## Nested Index or match formulas?

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 no Invoiced on Month unit price 3916 August 2015 32.62 3916 August 2015 32.62 3916 August 2015 32.62 3916 August 2015 32.62 3916 August 2015 32.62 3916 November 2015 28.54 3916 November 2015 28.54 3916 December 2015 28.54 3916 December 2015 28.54 3916 January 2016 28.54 3916 February 2016 28.54 3916 March 2016 28.54 3916 March 2016 32.62 3916 March 2016 32.62 3916 April 2016 32.62 3916 April 2016 32.62 3916 May 2016 28.54 3916 May 2016 28.54 3916 May 2016 28.54
 Search Criteria Cost item No. QUANTITY_1_PRICE Retruned result 46 0 3839 150.6 3847 25.3 3849 25.3 3855 29.26 3856 29.26 3864 29.26 3866 89.69 3870 29.26 3878 45.24 3882 45.24 3886 35 3893 55 3894 24.24 3900 18 3907 0 3911 0 3916 28.54 May 2016 3921 30
Thanks in advance for any thoughts on this.

2. ## Re: Nested Index or match formulas?

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),"")

3. ## Re: Nested Index or match formulas?

Originally Posted by 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),"")

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.

4. ## Re: Nested Index or match formulas?

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),"")}

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•