Data lookup based on multiple conditions without using CSE

[JHE1969]

I have a data table of dates, first names, surnames and salary. I need to be able to lookup the salary on or before a given date. I need to do this multiple times so do not want to use the Control Shift Enter array construct. Any suggestions for a single line formula that can deal with the data conditionality. An example table is below:

Date	FirstName	Surname	Salary
01/01/2018	John	Smith	102
01/02/2018	Jack	Jones	98
01/03/2018	Jill	Smith	52
01/04/2018	Tom	Jones	69
01/05/2018	Tom	Smith	12
01/06/2018	John	Smith	103
01/06/2018	Tony	Jones	54
01/06/2018	Jill	Smith	270
01/07/2018	Jill	Jones	103
01/07/2018	Jack	Jones	120
01/08/2018	Jill	Jones	78
01/09/2018	Tony	Smith	83
01/10/2018	Tom	Jones	47
Date	01/09/2018
FirstName	Tom
Surname	Smith
Salary

<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>

 

[shg]

What does "on or before" mean? If the individual got a raise on the specified date, he makes more money on that date than the day before.

 

[shg]

Assuming the intent is what they were making on the specified date,

	A​	B​	C​	D​	E​
1​	Date​	FirstName​	Surname​	Salary​	​
2​	1/1/2018​	John	Smith	102​
3​	1/2/2018​	Jack	Jones	98​
4​	1/3/2018​	Jill	Smith	52​
5​	1/4/2018​	Tom	Jones	69​
6​	1/5/2018​	Tom	Smith	12​
7​	1/6/2018​	John	Smith	103​
8​	1/6/2018​	Tony	Jones	54​
9​	1/6/2018​	Jill	Smith	270​
10​	1/7/2018​	Jill	Jones	103​
11​	1/7/2018​	Jack	Jones	120​
12​	1/8/2018​	Jill	Jones	78​
13​	1/9/2018​	Tony	Smith	83​
14​	1/10/2018​	Tom	Jones	47​
15​
16​
17​	1/9/2018​	Tom	Smith	12​	D17: =INDEX($D$2:$D$14, MATCH(1, INDEX((A17 >= $A$2:$A$14) * (B17 = $B$2:$B$14) * (C17 = $C$2:$C$14), 0), 0))

 

[JHE1969]

to clarify I'm not trying to find their current salary I want to find their last payment i.e. the final column is not their salary p/a but individual salary payments. by way of example if I entered Jack Jones and 1/6/18 I would want to return 98 but if I changed date to 1/7/18 then I would want to return 120. does that make sense ?

 

[shg]

	A​	B​	C​	D​	E​
1​	Date​	FirstName​	Surname​	Salary​	​
2​	1/1/2018​	John	Smith	102​
3​	1/2/2018​	Jack	Jones	98​
4​	1/3/2018​	Jill	Smith	52​
5​	1/4/2018​	Tom	Jones	69​
6​	1/5/2018​	Tom	Smith	12​
7​	1/6/2018​	John	Smith	103​
8​	1/6/2018​	Tony	Jones	54​
9​	1/6/2018​	Jill	Smith	270​
10​	1/7/2018​	Jill	Jones	103​
11​	1/7/2018​	Jack	Jones	120​
12​	1/8/2018​	Jill	Jones	78​
13​	1/9/2018​	Tony	Smith	83​
14​	1/10/2018​	Tom	Jones	47​
15​
16​
17​	1/6/2018​	Jack	Jones	98​	D17: =LOOKUP(9E+307, $D$2:$D$14/((A17>=$A$2:$A$14)*(B17=$B$2:$B$14)*(C17=$C$2:$C$14)))
18​	1/7/2018​	Jack	Jones	120​

 

[JHE1969]

Thanks, could you explain what the 1 as the first argument of the MATCH does please

 

[JHE1969]

That's great, what is the first argument in the LOOKUP ?

	A​	B​	C​	D​	E​
1​	Date​	FirstName​	Surname​	Salary​
2​	1/1/2018​	John	Smith	102​
3​	1/2/2018​	Jack	Jones	98​
4​	1/3/2018​	Jill	Smith	52​
5​	1/4/2018​	Tom	Jones	69​
6​	1/5/2018​	Tom	Smith	12​
7​	1/6/2018​	John	Smith	103​
8​	1/6/2018​	Tony	Jones	54​
9​	1/6/2018​	Jill	Smith	270​
10​	1/7/2018​	Jill	Jones	103​
11​	1/7/2018​	Jack	Jones	120​
12​	1/8/2018​	Jill	Jones	78​
13​	1/9/2018​	Tony	Smith	83​
14​	1/10/2018​	Tom	Jones	47​
15​
16​
17​	1/6/2018​	Jack	Jones	98​	D17: =LOOKUP(9E+307, $D$2:$D$14/((A17>=$A$2:$A$14)*(B17=$B$2:$B$14)*(C17=$C$2:$C$14)))
18​	1/7/2018​	Jack	Jones	120​

<tbody>
</tbody>

 

[shg]

When Excel does arithmetic operations (here, multiplication) with Boolean expressions, in converts TRUE to 1 and FALSE to 0, so the lookup array is a bunch of 0s and 1s. The 1 is the lookup value.

However, you want the formula in post 5.

 

[JHE1969]

When Excel does arithmetic operations (here, multiplication) with Boolean expressions, in converts TRUE to 1 and FALSE to 0, so the lookup array is a bunch of 0s and 1s. The 1 is the lookup value.

However, you want the formula in post 5.

Yep the formula in Post 5 works perfectly, thanks so much. Could I ask for an explanation as to the format of the LOOKUP please so I learn how to use this better going forward. Thanks again !

 

[shg]

The lookup vector contains the salary amounts when the names match and the specified date is >= the dates in the table, and a #DIV/0 error otherwise.

The LOOKUP function, which has a lookup value larger than any possible result, matches the last value where those conditions are true.

There's no need to quote my posts back to me.