Index - Match - LOOKUP. How to get the status of every customer at the closest date

Hi you all! I 've been killing myself trying to figure out how to get this done. I presume that the best way to do it is with INDEX -Match but I am not very good dealing with this formula.

The issue is as following.

For each customer ID I need to get his latest status before the date given. So my spreadsheet is as follow..

Customer ID Reporting date Status
66666666 01/06/2018 (Information to retrieve)

In the other spreadsheet I have a massive data with different customers with as many rows as dates were reported. An example

Customer ID Frequency Balance Date Balance Amount € Status
66666666 W 15-Jun-18 10 9.99999999999999A
66666666 W 16-Feb-18 14 9.99999999999999E

I would really appreciate if anyone can give me a hand with this. I ve tried many different ways unsuccessfully and I am doing manually (nightmare)

An example is attached. I hope to have explained myself correctly.

Thanks a mil

Thank you very much canapone. Works perfectly

Could you explain in words exactly what is happening and how with these formulas, for next time. I ve tried the formula valuation but still don't understand the beginning of the lookup formula (LOOKUP(2,1/((...).... I suppose that index-match wouldn't work with this issue, right?

Regards,

Hi,

The formula definitely works but it is not taking the closest date before the date given. How should I amend the formula?

Sample is attached

Thanks

Try this formula, entered as an array formula with ctrl+shift+enter:

Please Login or Register  to view this content.

it works lovely, thank you very much Melvosh. I've doing some training with index-match since lat weekend, So I am beginner with that formula. i understand the logic, but it seems tough to me to get the dynamic. Could you explain a bit this part of the formula..

((Sheet2!$K$2:$K$22766=MAX(IF(Sheet2!$A$2:$A$22766=$A2,IF(Sheet2!$K$2:$K$22766<=$B2,Sheet2!$K$2:$K$22766))))

Thanks once again.

That part of the formula narrows down the balance date in your data to the max date that is less than or equal to the date you're looking for, while still matching all of the necessary criteria. I'd prefer to do it a little simpler, but MAX doesn't play very nicely with INDEX / MATCH array formulas, which is why the nested IF statements are needed.

I hope that explanation is helpful (and coherent).