Index Match with Multiple Criteria (incl. wildcard)

[chrisou]

HI Guys

I am trying to use the INDEX MATCH based on multiple criteria including a wildcard (partial text and an *).
In the attached excel file, the main data is in the PROD tab.
I am trying to summarise this data in the Prod summary tab but I am having problems working out the formulas for the columns in orange.

I wish to know for each of the different packaging formats (Gn 1/3, bol, verrine) when the first and last product of each format was produced for each day.
I have put in formulas for the first and last products of the day but when I try to adapt these formulas to include the packaging type (Gn 1:3, bol, verrines, etc) I receive a N/A message. ;-(

I would really appreciate your help to assist me in understanding what I am doing wrong

Many thanks

Chris

[Shawnhe]

These formulae should get you close - Array entered for GN product

{=MIN(IF((PROD!A:A=A4)*(PROD!C:C="GN 1/3 LENT"),PROD!B:B))} for start time
{=MAX(IF((PROD!A:A=A4)*(PROD!C:C="GN 1/3 LENT"),PROD!B:B)))for start time of last batch of GN not when batch is done - if this isn't what you are looking for let me know.

Cheers,
Edit: Sorry missed that some of the rows don't produce any output - add the boolded text to both formulae.
{=MIN(IF((PROD!A:A=A4)*(PROD!C:C="GN 1/3 LENT"*(PROD!I:I>0)),PROD!B:B))}

[chrisou]

Hi Shawnhe

Many thanks for your reply which works with the formulas proposed. I originally thought of using INDEX MATCH but MIN / MAX IF works excellently. Thank you.

However, in the original file, I could have a packaging BOL 130 LENT and BOL 130 MOYEN and I am interested in knowing when the packaging BOL 130 starts or finishes (independantly of whether it is LENT or MOYEN). I have tried to adapt your formula entering an * to replace part of the criteria as in the formula below but I get a 00:00:00 time..! How could I take into account only part of the text search in column C?

={MIN(IF((PROD!A:A=A4)*(PROD!C:C="BOL*")*(PROD!I:I>0);PROD!B:B))}

Many thanks for you help

Chris

[Shawnhe]

Unfortunately, the wildcard doesn't seem to work well with the array functions. Newer versions of excel have formulae that will work with it. (MINIFS, MAXIFS) but those won't work on your version of excel.

I'm not sure how to work around this at the moment - I tried the following which I thought should work as it evaluates all three types of BOL but I am not getting the correct answer. It is possible that my syntax is wrong somehow or I have made some other error. Perhaps you can take a look at this and figure it out - it may be something simple.

=MIN(MIN(IF((PROD!A:A=A4)*(PROD!C:C="BOL 130 MOYEN")*(PROD!I:I>0),PROD!B:B)),MIN(IF((PROD!A:A=A4)*(PROD!C:C="BOL 130 LENT")*(PROD!I:I>0),PROD!B:B)),MIN(IF((PROD!A:A=A4)*(PROD!C:C="BOL 130 RAPIDE")*(PROD!I:I>0),PROD!B:B)))

Anyways, I will think about this a bit more or maybe someone else will be able to see the issue.

Good luck.

[chrisou]

Hi Shawnhe
Thanks for your reply and for trying to find a solution. It seems that array formuas do not like text strings or wldcards in excel 2013. Trying to look for an alternative solution with count ifs etc
Chris

[Glenn Kennedy]

Don't use array formulae with whole columns... it will make your sheet very slow.

[Glenn Kennedy]

Try:

=IFERROR(INDEX(PROD!$B:$B,AGGREGATE(15,6,ROW(PROD!$B$2:$B$434)/((PROD!$A$2:$A$434=$A4)*(ISNUMBER(SEARCH("Gn 1/3",PROD!$C$2:$C$434)))),1)),"")

and

=IFERROR(INDEX(PROD!$B:$B,AGGREGATE(14,6,ROW(PROD!$B$2:$B$434)/((PROD!$A$2:$A$434=$A4)*(ISNUMBER(SEARCH("Gn 1/3",PROD!$C$2:$C$434)))),1)),"")

for 1st and last, respectively. see file for the set.

[chrisou]

HI Glenn
This works perfectly. Many thanks
Chris

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.