Formula to find value between rows with matching data

[Jupiter1]

Hi All,

I'm trying to get a formula that retrieves an amount from Sheet 1 based on looking at the dates between rows specified for each persons ID. The problem I have is the format of data, as the ID is not against each date. Is there a way to lookup between rows containing certain values? So for instance... on Sheet 2 I want to be able to type a specific date into a cell say the 10/1/20 & it will find the corresponding amount to matching date of each persons ID. So for Jenny (between rows containing her ID 3) it would be 8 & Tony (between rows containing his ID 4)would be 5.

Hope it is possible and appreciate any help on this.

Many thanks

[#2]

@Jupiter1 

Maybe I am not the right one to suggest a solution in this direction. But with your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

Hi@NikolinoDE

Thank you for your prompt reply and the suggestion to upload a file.

There is now a file attached with a few notes to explain my goal.

Many thanks

[#4]

@Jupiter1 Are you familiar with "Get&Transform Data" (a.k.a. Power Query)? Then, it will be not so difficult to extract the information you need. 

Alternatively, if you are an MS365 subscriber with access to the latest functions like FILTER and UNIQUE, it's relatively easy as well with some helper columns.

[#5]

Hi@Riny_van_Eekelen 

Luckily I have access to filter and unique function....so I'm excited it is achievable.

Look forward to learning more

Thanks

[#6]

@Jupiter1 Have a look at the attached! Perhaps not very pretty, but it works.

[#7]

Hi@Riny_van_Eekelen

Fantastic....That is amazing!!!! It works like a dream now.

Thank you so so much for all your help.

[#8]

@Jupiter1 You're welcome!!