Need formula to sort column based on same info on another worksheet, in different order

I have two worksheets, one is Pipeline (a list of businesses I call), the other is Contact Log (each row is each call). On the Contact Log page (see attached) I have a Status column. The status must be chosen from an in-cell dropdown I created in Data Validation (three options). I would like the businesses on the Pipeline worksheet (see other attachment) to be sorted by the Status chosen of the latest contact with the business on the Contact Log worksheet.

contact log page.jpg

pipeline page.jpg

I have played with the Lookup function, and this formula worked on the topmost occurence of two businesses: =LOOKUP(Pipeline!C3:C700,'Contact Log'!E5:E700,'Contact Log'!H5:H700). but not in most of the others. I read something that they must be in the same order, the business names, but I need the contact log to be in descending order of date/time, so I can't accomodate that.

Any ideas on how I can achieve what I'm looking for? I basically want the status column on the pipeline page to look up the business name of that row, on the contact log page, and bring back the status chosen on the latest (bottom) occurence of the business name, and then sort the rows on the pipeline page by the status, with the blank (no status chosen) at the bottom and the others on the top. Does that make sense?

contact log page.jpgpipeline page.jpg

I am not familiar with LOOKUP. I suggest you to use VLOOKUP.

For providing inputs submit an excel file instead of images. Please read forum rules for clarification.

Sorry. My earlier reply is incomplete. VLOOKUP can only bring in a matching or near value from another range. Your need is to bring a column value based on max value of date in the second range based on a criteria in the first range. I have created a sample worksheet with 2 ranges to provide an example. There are certain additional tasks that need to be performed to get this done. May be some easier methods can be suggested by other members, but this is a working method for you to use now.

1) Bring contact date in your first range. Use MAX and IF in an array formula. (If you have not used Array formula so far, please search for tips on using array formula. You need to be careful in submitting array formula in a worksheet by typing a normal formula with =.... and using keys Ctrl+Shift+Enter
2) Create an additional column in your second range for concatenating CustomerID and StatusDate (It is always better to use some special characters, which are delimiting the fields and not used in data of the columns concatenated)
3) VLOOKUP and fill status in first range using same concatenation method used in the second range.

Sample Excel attached.