mrMadCat
New Member
- Joined
- Jun 8, 2016
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
Hello. I'm going round and round with vlookup, lookup, index, match, sumproduct etc. but can't find the right solution.
I have a list with prices for clients and dates from which they start to be active:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]01.06.2016
[/TD]
[TD]client1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]01.07.2016
[/TD]
[TD]client1
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]01.08.2016
[/TD]
[TD]client1
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]01.06.2016
[/TD]
[TD]client2
[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]01.06.2016
[/TD]
[TD]client3
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]10.07.2016[/TD]
[TD]client1[/TD]
[TD]=?[/TD]
[/TR]
</tbody>[/TABLE]
I need to find the right price for the exact date and client, lets say cell C10.
I like how lookup works but it has only 1 criteria. I need a formula that is light, probably not array formula because I need to use it on a big data.
sure I'm not the first to ask it, but as it says "to know what to look for is to know the answer".
Thank you.
I have a list with prices for clients and dates from which they start to be active:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]
Start_Date
[/TD][TD]
Client
[/TD][TD]
Price
[/TD][/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]01.06.2016
[/TD]
[TD]client1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]01.07.2016
[/TD]
[TD]client1
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]01.08.2016
[/TD]
[TD]client1
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]01.06.2016
[/TD]
[TD]client2
[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]01.06.2016
[/TD]
[TD]client3
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]10.07.2016[/TD]
[TD]client1[/TD]
[TD]=?[/TD]
[/TR]
</tbody>[/TABLE]
I need to find the right price for the exact date and client, lets say cell C10.
I like how lookup works but it has only 1 criteria. I need a formula that is light, probably not array formula because I need to use it on a big data.
sure I'm not the first to ask it, but as it says "to know what to look for is to know the answer".
Thank you.