Lookup for data with 2 creterias (date and name)

[mrMadCat]

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:

	A	B	C
1	Start_Date​	Client​	Price​
2	01.06.2016	client1	1
3	01.07.2016	client1	2
4	01.08.2016	client1	3
5	01.06.2016	client2	7
6	01.06.2016	client3	6
7
8
9
10	10.07.2016	client1	=?

<tbody>
</tbody>

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.

 

[Eric W]

Maybe:

	A	B	C
1	Start_Date	Client	Price
2	6/1/2016	client1	1
3	7/1/2016	client1	2
4	8/1/2016	client1	3
5	6/1/2016	client2	7
6	6/1/2016	client3	6
7
8
9
10	7/10/2016	client1	2

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula C10 =LOOKUP(A10,1/(1/A2:A9*(B2:B9=B10)),C2:C9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[mrMadCat]

Yes It works. Thank you. But I don't understand the syntaxis - instead of looking vector there are formula-array combinations that should not work in standard excel logic if not an array formula. Where can I look for some info on what it is and how it is working?

 

[Eric W]

Very true. While not technically an array formula, this formula does array processing. There are a few functions that do that: LOOKUP, SUMPRODUCT, and depending on your definition, functions like VLOOKUP or COUNTIF.

LOOKUP is an older function that theoretically has been replaced by VLOOKUP and HLOOKUP. It's kept for backward compatibility, but there are a couple of semi-undocumented features about it that make it very valuable. First is the array processing, and second is the fact that it will ignore error values.

I'll walk through how the example works:

=LOOKUP(A10,1/(1/A2:A9*(B2:B9=B10)),C2:C9)
First it checks to see which rows match the client, and generates an array of TRUEs and FALSEs.

=LOOKUP(A10,1/(1/A2:A9*({TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})),C2:C9)
Now it pulls in the dates (and I'm sure you're aware that Excel stores dates as a number)

=LOOKUP(A10,1/(1/{42522,42552,52583,42522,42522,0,0,0}*{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}),C2:C9)
Now it multiplies them together. When multiplying by TRUE/FALSE, TRUE=1 and FALSE=0

=LOOKUP(A10,1/(1/{42522,42552,52583,0,0,0,0,0}),C2:C9)
Now we have the dates that match the client. Now we use a trick so that the zeros turn into errors, which LOOKUP will ignore. If you take the reciprocal of a number (1/x) twice, you get the original number back. But if the original number is zero, then 1/0 will generate #DIV/0 and 1/#DIV/0 is still #DIV/0

=LOOKUP(A10,{42522,42552,52583,#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0},C2:C9)
Now we pull the data from column C.

=LOOKUP(A10,{42522,42552,52583,#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0},{1,2,3,7,6,0,0,0})
Get the lookup value.

=LOOKUP(42561,{42522,42552,52583,#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0},{1,2,3,7,6,0,0,0})
And finally we have it in the "basic" format of a LOOKUP. The LOOKUP finds the first position in the first array where the lookup value is greater than or equal to the array value and less than the next array value, and returns the value from the second array in the same position.

2

So this is not technically an array formula, but it does have array processing. I can't say how the performance will work on your sheet, but since the array processing is inherent in LOOKUP, it'll probably be fairly fast.

Hope this helps!

 

[mrMadCat]

Eric W, thank you for the detailed and interesting explanation. Now it looks clear. Though I think 1/1/ trick is not really necessary in this situation, anyway good to know such a trick.
I finally used a modified version of your formula:
=LOOKUP(2;1/(A2:A9<=A10)/(B2:B9=B10);C2:C9)
Thank you again very much.

 

[Eric W]

Nicely done. Glad to help.

 

[mrMadCat]

Hello again! )
I returned to this formula i na similar situation. Only this time second criteria is not text (client) but a number (not always equeal to the one in index table).
I tried to modify it to =LOOKUP(2;1/(A2:A9<=A10)/(B2:B9<=B10);C2:C9) but it doesn't work. What did I do wrong?