# Multiple column query to return value of third column

#### mattfj

##### New Member
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
39 KB · Views: 6

### Excel Facts

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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))

Replies
1
Views
102
Replies
5
Views
192
Replies
6
Views
229
Replies
3
Views
110
Replies
0
Views
55

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.

### Which adblocker are you using?

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

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