How to find a specific value using multiple criteria

Roar70

New Member
Joined
Mar 29, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Maybe something like this:

Book2
ABCDEFGH
1Field1Field2DateField1Field2Max Date
2Test1a3/25/2023FindTest1a3/29/2023
3Test2b3/26/2023
4Test3c3/29/2023Test1a3/29/2023
5Test4d3/28/2023
6Test1a3/29/2023
7Test3f3/30/2023
8Test4d3/31/2023
9Test1a3/28/2023
Sheet1
Cell Formulas
RangeFormula
H2H2=MAX(FILTER($C$2:$C$9,($A$2:$A$9=$F$2)*(B2:B9=$G$2)))
E4:G4E4=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.
 
Upvote 0
Maybe something like this:

Book2
ABCDEFGH
1Field1Field2DateField1Field2Max Date
2Test1a3/25/2023FindTest1a3/29/2023
3Test2b3/26/2023
4Test3c3/29/2023Test1a3/29/2023
5Test4d3/28/2023
6Test1a3/29/2023
7Test3f3/30/2023
8Test4d3/31/2023
9Test1a3/28/2023
Sheet1
Cell Formulas
RangeFormula
H2H2=MAX(FILTER($C$2:$C$9,($A$2:$A$9=$F$2)*(B2:B9=$G$2)))
E4:G4E4=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....
 
Upvote 0
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!
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top