Formula required to reference date range

[ettyenn]

Hello all. Really stuck on a formula and would love it if anyone had any ideas. Excel sheet attached demonstrating the problem.

Data return Tab shows a list of contracts and the date the contract was invoiced. The problem is, there are multiple amendments to each contract (Amendment info tab) and I need the formula to look up the contract number and the invoice date and then look at the amendment table to return the correct contract amendment.

This is easy to do for one contract at a time like this https://www.extendoffice.com/documen...ate-range.html
however this does not work once you have lists of multiple contract numbers.

I am racking my brains on this and if I could figure this out it would be a momentous time saver. Any suggestions are welcome and if I havent explained myself sufficiently then please do let me know.

Thanks in advance

[Sam Capricci]

this appears to work...
=LOOKUP(2,1/('AMENDMENT INFO'!$A$3:$A$128=A4)/('AMENDMENT INFO'!$C$3:$C$128<=B4)*('AMENDMENT INFO'!$D$3:$D$128>=B4),'AMENDMENT INFO'!$E$3:$E$128)

[Olly]

You could do it with a horrible array formula:

In 'DATA RETURN'!C4:

Please Login or Register  to view this content.

...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.

[Olly]

You could, of course, use Power Query.

Format your source data as tables (named "Invoices" and "Amendments"), then use:

Please Login or Register  to view this content.

Example attached.