Finding the closes date - multi crateria

[atlant15]

Hi,

if i have a list of two (or more) items, which i wrote next to them (in a different cell) the purchase date and the amount.
on a different cell i want to write a date to get all the purchases made that chosen date.
if i haven't bought one or more items on that date i still want it to show the last purchase of an item.

example (this is my list):
(item - date - amount)

apples - 01/05/2016 - 1
apples - 02/05/2016 - 7
apples - 03/05/2016 - 2
cherry - 01/05/2016 - 3
tomato - 01/05/2016 - 1
tomato - 02/05/2016 - 3

if i write down:
01/05/2016, i want it to show:
apples - 1
cherry - 3
tomato - 1

but if i write down 03/05/2016, i want it to show:
apples - 2
cherry - 3
tomato - 3

(the two last items on the list haven't been purchased on 03/05/2016 but it shows the data from closest purchase date)

the question is: what is the formula to finding the last closest date for each item.

(i have the item list ready - i don't need to find the items).

thank you very much

[oeldere]

looks to me it can be done with a pivot table.

[atlant15]

it can be, but i need a formula that can do it every time i just adding data

[oeldere]

with a pivot table you just have to refresh the data. Simple, fast and reliable.

[atlant15]

excellent, but i don't need a pivot table, i need a formula...
what i wrote is an example, in reality i have a very long list. i add data there on a daily basis and it goes to several reports, that i can change the date to any date.
it should be automatic.

[oeldere]

Please Login or Register  to view this content.

That is also a very good reason using pivot table.

[oeldere]

1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[atlant15]

attached,

the list is on the left. what i want it to show is on the right.

if i choose Option 1 (for example) - in cell 'I3', i want to have the formulas in column 'F' show the date, if the date is the same as the one i choose or the closest.

if i choose option 2 - in cell 'L3', i want to have the formulas in column 'G' show the date, if the date is the same as the one i choose or the closest.
as you can see, it is empty in cells 'G11' & 'G14' - highlighted.

i need a formula that checks if the date is the same as i chose, and if not still put the same date (05/05/2016 - in that example) on the last date data.

thanks

[oeldere]

For the first 1 used 2 helpcolumns

J5 =INDEX($B$3:$E$14,MATCH(I5&I$3,$B$3:$B$14,0),4)

For the second you can use this one

M5 =VLOOKUP(L$3,INDIRECT(N5&":"&O5),2,TRUE)

See the attached file.

[atlant15]

thank youvery much, but i don't think it is the file you meant to upload

[oeldere]

I already changed the file, see post 9.

[oeldere]

@atlant15

I would appreciate it, if you comment on the solution in #9.