Seaching for last date that meets requirements

[Befuddled]

I am hoping to find a way to search for the entry in sheet "Jan" that has the newest date in column B, that also has a 101, 103, or 121 in column E,
and is sorted for either a 1,2, or 3 in column G.

I have attached an example workbook.
The data is in sheet "Jan" and the results in sheet "Spot PMs"

I filled in the expected results in "Spot PMs" B3 and C3

I will end up with a sheet for each month, with 3000 to 4000 rows of entries each.
With 300 to 400 different "Die Numbers" in column B.

Thanks for any help you can give.

Mike

[FDibbins]

This ARRAY formula will give you the 1st 1 you wanted...
=LARGE(IF((Jan!$D$2:$D$28=Jan!$O3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1)


Im not sure about the 2nd 1, but maybe just change the bolded 1 to 2 or 3?...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. Press F2 on that cell and try again.

[Befuddled]

That works great.
I added the formula in "Spot PMs" D3 to look for a 3 in "Jan" column G.
How would I modify the Large part of the formulas in B3 and C3 to also look to see if the date in D3 is higher?

[FDibbins]

I modified the formula to look at the date...
=LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=Jan!$P$1)*(Jan!$D$2:$D$28=Jan!$O3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1)

Still CSE

[Befuddled]

I tried adding the items in BOLD, but it gives a #VALUE! error
=LARGE([B]([B]IF((Jan!$D$2:$D$28=Jan!$O3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),D3),Jan!$B$2:$B$28),1)

[FDibbins]

Not really sure what you are trying to do there? The modified formula I gave you will test for "Jan" as well as for all the other stuff, just replace my 1st formula with the 2nd formula

[Befuddled]

I'm not sure why it is not working for me.
I get a #NUM! error when I copy and paste your formula into "Spot PMs" B3
What is the formula looking for in Jan!$P$1 and Jan!$O3 ?

Sorry about the jumble in previous post, I was just trying to make the changes I made show up in BOLD print

[FDibbins]

My apologies, I had copied your table from sheet2 over to sheet1 so I could more easily see what I was working with when I constructed the formula. I then copied it back to sheet2, but neglected to adjust some of teh references It shouild have been this,....
=LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=$B$1)*(Jan!$D$2:$D$28=$A3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1)

[Befuddled]

Is there a way to combine the formulas(in the new attached workbook) in "Spot PMs" B3 and E3 in B3 (and C3 and F3) so I do not have to have 2 more columns of formulas?

[FDibbins]

Perhaps this?
=MAX(LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=$B$1)*(Jan!$D$2:$D$28=$A3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1),LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=$B$1)*(Jan!$D$2:$D$28=$A3)*(Jan!$G$2:$G$28=3)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1))
Still CSE

This is just testing to see which of those 2 formulas returns the highest value (date)...
=MAX(
LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=$B$1)*(Jan!$D$2:$D$28=$A3)*(Jan!$G$2:$G$28=1)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1),
LARGE(IF((TEXT(Jan!$B$2:$B$28,"mmm")=$B$1)*(Jan!$D$2:$D$28=$A3)*(Jan!$G$2:$G$28=3)*(Jan!$E$2:$E$28={101,103,121}),Jan!$B$2:$B$28),1))

[Befuddled]

That is awesome, and works perfect.
Thanks for all your effort.

I hate to monopolize your time, but is there a way to have the formula look at a named range instead of {101,103,121} ?

[FDibbins]

As far as I know, you cannot use range names for this kind of array

[Befuddled]

OK.
Again, thanks for all your help, I have been beating my head on this for a week, my searches on this forum had not been very fruitful.

Mike

[FDibbins]

Yes, the search facility on this forum (and others that I frequent) are often not of the best

It was fun helping you, thanks for the feedback

[Befuddled]

Hate to bother you again, but if the formula does not find both a 1 and a 3, it returns a #NUM! error.
If you delete the "3" in G18, it throws the errors