How to look-up data based on date criteria.

Please help.

I need a 2 formula
1.) to look up the "ORDER" the corresponds to date sale
2.) to look up the "DATE SALE" nearest after date of input

CRITERIA:
1.) REF and DATE OF INPUT

i have done sumproduct but it seems it does capture criteria for nearest date.

Formula:

Please Login or Register  to view this content.

I have tired this formula but it does not completely works.

Formula:

Please Login or Register  to view this content.

I have been trying to attach file. I cannot attach for both explorer and chrome. my desired out is the AFTER in the image. that was manually encoded.1.png

Hi,
1.png
with desired output

test attchment

hi, i have attached now. please have a check

Originally Posted by JohnTopley

If you follow the instructions #3, you will be able to attach a file.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

Originally Posted by JohnTopley

In M3

=IFERROR(INDEX($F$14:$F$21,SMALL(IF(($H$14:$H$21=$L3)*($G$14:$G$21>=$K3),ROW($F$14:$F$21)-ROW($F$14)+1,""),1)),"")

in N3

=IFERROR(INDEX($G$14:$G$21,SMALL(IF(($H$14:$H$21=$L3)*($G$14:$G$21>=$K3),ROW($F$14:$F$21)-ROW($F$14)+1,""),1)),"")

both ..

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Its working now. thank you so much. I will add new reputation for you.

Originally Posted by AliGW

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

Thanks you for reminding me.