[VBA] Custom lookup- using arrays?

[completebhejafry]

Hey!

I wrote a custom lookup function to help me out. Its a little difficult to explain, so please look at the uploaded file here.


https://www.dropbox.com/s/sfb7crrql21n26p/for%20upload%20to%20forum.xlsm


The cells highlighted in yellow are what I am looking up. If you notice, the snapshot I've shared has the same lookup value.


In such a case, there are a lot of ways I can separate it out. The one I am unable to implement is:

1. The date highlighted in blue must be as recent as possible.

2. The date highlighted in orange must be in the future.


How do I do this?


PS: I've put the complete code I'm using in the file. Please see if I can do this with minimal change to this code.

 

[SirJB7]

Hi, completebhejafry!


Looking at your uploaded file "for upload to forum.xlsm" there's no correlation between the sheet's names (Sheet1, Sheet2, Sheet3) and the worksheet's names used in Sub find_cost.


Besides, as you wrote before, I wasn't able to find any data highlighted.


Am I wrong? If yes, please upload a sample file with VBA code fully functional so as people who read this might be able to check it.


Regards!