Return oldest date in range using asterisk as criteria

I'm having trouble with my formula in Sheet1!C:C which I believe is due to the fact that I'm using an asterisk as part of the search criteria. I previously used specific criteria e.g. "Kitchen" however due to multiple departments falling under the parent departments (detailed on Sheet3), I need the formula to look for any data beginning with a K. This method worked in Sheet1!B:B using a COUNTIF function, however it doesn't seem to work in my array.

Try

=IF(B2=0,"-",MIN(IF(LEFT(Sheet2!$A:$A)=LEFT(A2),Sheet2!$B:$B)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

then copy down

You're right. You cannot use a wildcard character like that.

Try this instead:

C2 =IF(B2=0,"-",MIN(IF(LEFT(Sheet2!$A$1:$A$100)="K",Sheet2!$B$1:$B$100))) Ctrl Shift Enter

Also notice that I changed the whole column references into a smaller range. You do not want to use whole column references in an array formula.

Thanks for this, I'll give this a shot as well as the suggestion from JohnTopley.

I've been told on this forum before not to use whole column references in an array, can I ask why? In the actual spreadsheet I'm using this in, I'm using the whole column and it's working. The problem with changing to a smaller range is that it's a monthly report and the number of entries can vary massively each month. Would it work if I did a range of B1:B10000? Or is that too large?

Here's another alternative assuming that your data is in chronological order like in your sample:

C2 =IFERROR(VLOOKUP(LEFT(A2)&"*",Sheet2!A:B,2,0),"-")

You can drag this down.

Your version of Excel has 1,048,576 rows. If you use whole column references in an array formula, the formula has to calculate through over 1 million rows.

Although this will usually work, it can slow the workbook down tremendously.

Using a range of B$1:B$10000 would be much better. 10,000 rows is less than 1% of the entire column.
Alternatively, you can convert your data into a table and refer to the table column, which will expand and contract automatically as you add or remove data.

That's beyond my capabilities. I'll go with the 1:10000 option. Thanks for explanation.

Happy to help. Thanks for the rep!

I shouldn't have marked this as solved so soon. My actual spreadsheet is more complex than this example. Please could you advise how I would add additional search criteria into the same formula? E.g. If parent department "Kitchen" had multiple child departments, one beginning with K as shown in the example, one beginning with X and one beginning with Z. If needed, I can amend the spreadsheet to show what I mean.

Originally Posted by glomb175

If needed, I can amend the spreadsheet to show what I mean.

If you share a more representative sample of your data along with the desired results based on that sample, we'll take a look.

Thank you, I've updated the attached so that the Kitchen department contains multiple sub departments each beginning with a different letter.

Here's one option:

Sheet2 C2 =INDEX(Sheet3!A:A,MATCH(A2,Sheet3!B:B,0))
Drag down column C.

Sheet1 C2 =IF(B2=0,"",MIN(IF(Sheet2!C$2:C$36=A2,Sheet2!B$2:B$36))) Ctrl Shift Enter
Drag down column C.

Is there another way? Just because in my original spreadsheet there are tens of thousands of individual sub divisions under each department.

Therefore the index would be enormous.

Your INDEX is Sheet3 so no change there: it is FIXED and yes, will be enormous !

In Sheet2 we have to relate back to your INDEX (as per solution offered) so we know the Parent/Child relationship..