How to find a specific value using multiple criteria

[Roar70]

Hello! I am a fairly new novice using excel. I find it very interesting, exploring all of the things that can be done with it. But now I find myself over my head.
I have a database that I have created on one of my worksheets and I am trying to extract specific values from certain fields using multiple criterias, one of which are dates. I have tried several different formulas trying to accomplish this with very little luck. I have three different fields that I am trying to match that finds all three matching variables. That phase of the formula works pretty well, but when I include the date field to try and find the latest date that matches the first three variables that will allow me to find the value in the field that contains the information i am looking for, and this is where everything goes wrong.

I have tried Index/Match, Vlookup, Max If and Maxifs formulas with no luck. I am pretty sure that one will work fine if I can put it in a formula that the computer understands.
Any help will be greatly appreciated.

 

[bebo021999]

How your data look like?
I assume you have three fields with three criterias, one of them is date
Field1, Field2, DateField
The out put range: OutputRange
Assume you want to get the value from OutputRange, where the three criterias match, with the latest date match

=LOOKUP(2,1/(Field1=criteria1)/(Field2=criteria2)/(DateField<=DateCriteria),OutputRange)

If it does not help, try to post image/Xl2BB mini sheet.

 

[Roar70]

How your data look like?
I assume you have three fields with three criterias, one of them is date
Field1, Field2, DateField
The out put range: OutputRange
Assume you want to get the value from OutputRange, where the three criterias match, with the latest date match

=LOOKUP(2,1/(Field1=criteria1)/(Field2=criteria2)/(DateField<=DateCriteria),OutputRange)

If it does not help, try to post image/Xl2BB mini sheet.

 

[Roar70]

Thank you for answering my post. I entered the formula and it is pretty close to what I am trying to do. The Lookup part works fine in finding the the two criteria of my datasheet but I do not want to add a specific date for it to search. I would like it to find the two criteria I have specified for field 1 and 2 and then find the latest date in the date field that matches the two criteria I have entered.
I hope that I have explained better what I am trying to accomplish. I would loved to have posted a xl2bb mini sheet, but unfortunately I have not mastered that procedure yet. Lots to learn. LOL!
Thanks you again for any additional help you can send my way.

 

[AhoyNC]

Maybe something like this:

Book2
	A	B	C	D	E	F	G	H
1	Field1	Field2	Date			Field1	Field2	Max Date
2	Test1	a	3/25/2023		Find	Test1	a	3/29/2023
3	Test2	b	3/26/2023
4	Test3	c	3/29/2023		Test1	a	3/29/2023
5	Test4	d	3/28/2023
6	Test1	a	3/29/2023
7	Test3	f	3/30/2023
8	Test4	d	3/31/2023
9	Test1	a	3/28/2023
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MAX(FILTER($C$2:$C$9,($A$2:$A$9=$F$2)*(B2:B9=$G$2)))
E4:G4	E4	=FILTER($A$2:$C$9,($A$2:$A$9=$F$2)*($B$2:$B$9=$G$2)*($H$2=$C$2:$C$9),"No Match")
Dynamic array formulas.

 

[Roar70]

Maybe something like this:

Book2
	A	B	C	D	E	F	G	H
1	Field1	Field2	Date			Field1	Field2	Max Date
2	Test1	a	3/25/2023		Find	Test1	a	3/29/2023
3	Test2	b	3/26/2023
4	Test3	c	3/29/2023		Test1	a	3/29/2023
5	Test4	d	3/28/2023
6	Test1	a	3/29/2023
7	Test3	f	3/30/2023
8	Test4	d	3/31/2023
9	Test1	a	3/28/2023
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MAX(FILTER($C$2:$C$9,($A$2:$A$9=$F$2)*(B2:B9=$G$2)))
E4:G4	E4	=FILTER($A$2:$C$9,($A$2:$A$9=$F$2)*($B$2:$B$9=$G$2)*($H$2=$C$2:$C$9),"No Match")
Dynamic array formulas.

Thank you AhoyNC. This did help me find the information in my Datasheet for the last entry of the criteria entered using the Max function. Now that I can find the record of the last entry I need to be able to I need to extract information from three additional fields. I tried to change your formula to help me get it but had no luck! Is there something I can add to your formula that would allow me just to extract the additional information. Thanks again....

 

[Roar70]

Thank you AhoyNC. This did help me find the information in my Datasheet for the last entry of the criteria entered using the Max function. Now that I can find the record of the last entry I need to be able to I need to extract information from three additional fields. I tried to change your formula to help me get it but had no luck! Is there something I can add to your formula that would allow me just to extract the additional information. Thanks again....

Hello Again....Just wanted to let you know that I finally figured it out. Thanks again...not sure I would have ever got it to work without you help!

 

[AhoyNC]

You're welcome. Thanks for the feedback.