Multiple column query to return value of third column

mattfj

New Member
Joined
Nov 18, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I am trying to write a formula that matches criteria in one column, that corresponds with a maximum date less than today in a second column, and returns the corresponding value from the third column. I've tried various combinations of VLOOKUP, MAX, INDEX, MATCH, SUMPRODUCT, AND, IF and I've gotten fairly close but I am stumped. Please help. Thanks.
 

Attachments

  • Example 1.JPG
    Example 1.JPG
    39 KB · Views: 6

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Note for the future: You will generally get faster/better responses in the forum if you provide your sample data with XL2BB and include the expected results.

See if this does what you want.
You mentioned that the data must be less than today but did not include any examples where the date is >= today
If the dates were all earlier than today like your samples then the formula could be simpler.
If you do have any follow-up questions it would also be useful to know if the data is always grouped by column A and with increasing dates for each group in column B like your samples.

20 12 12.xlsm
ABCDEFG
1FruitDateDescriptionFruitAnswer
2Apple1-Dec-20GoodAppleCrunchy
3Apple3-Dec-20SweetOrangeCitric
4Apple8-Dec-20CrunchybananaSoft
5Orange1-Dec-20Sour
6Orange3-Dec-20Citric
7Banana8-Dec-20Soft
8Banana5-May-21Unknown
Most Recent
Cell Formulas
RangeFormula
G2:G4G2=INDEX(C:C,AGGREGATE(14,6,ROW(C$2:C$8)/((A$2:A$8=E2)*(B$2:B$8=AGGREGATE(14,6,B$2:B$8/((A$2:A$8=E2)*(B$2:B$8<TODAY())),1))),1))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

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
Top