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:

[TABLE="width: 348"]
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]Date[/TD]
[TD="class: xl65, width: 87"]FirstName[/TD]
[TD="class: xl65, width: 87"]Surname[/TD]
[TD="class: xl65, width: 87"]Salary[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/01/2018[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]102[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/02/2018[/TD]
[TD="class: xl65"]Jack[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]98[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/03/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]52[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/04/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]69[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/05/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]Tony[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]54[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]270[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/07/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/07/2018[/TD]
[TD="class: xl65"]Jack[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]120[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/08/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]78[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/09/2018[/TD]
[TD="class: xl65"]Tony[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]83[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/10/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Date[/TD]
[TD="class: xl66, align: right"]01/09/2018[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]FirstName[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Surname[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Salary[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

FirstName​

[/td][td="bgcolor:#F3F3F3"]

Surname​

[/td][td="bgcolor:#F3F3F3"]

Salary​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1/1/2018​

[/td][td]John[/td][td]Smith[/td][td]

102​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1/2/2018​

[/td][td]Jack[/td][td]Jones[/td][td]

98​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1/3/2018​

[/td][td]Jill[/td][td]Smith[/td][td]

52​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1/4/2018​

[/td][td]Tom[/td][td]Jones[/td][td]

69​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1/5/2018​

[/td][td]Tom[/td][td]Smith[/td][td]

12​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1/6/2018​

[/td][td]John[/td][td]Smith[/td][td]

103​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1/6/2018​

[/td][td]Tony[/td][td]Jones[/td][td]

54​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1/6/2018​

[/td][td]Jill[/td][td]Smith[/td][td]

270​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1/7/2018​

[/td][td]Jill[/td][td]Jones[/td][td]

103​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1/7/2018​

[/td][td]Jack[/td][td]Jones[/td][td]

120​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1/8/2018​

[/td][td]Jill[/td][td]Jones[/td][td]

78​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

1/9/2018​

[/td][td]Tony[/td][td]Smith[/td][td]

83​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1/10/2018​

[/td][td]Tom[/td][td]Jones[/td][td]

47​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

1/9/2018​

[/td][td]Tom[/td][td]Smith[/td][td="bgcolor:#CCFFCC"]

12​

[/td][td]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))[/td][/tr]
[/table]

 

[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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

FirstName​

[/td][td="bgcolor:#F3F3F3"]

Surname​

[/td][td="bgcolor:#F3F3F3"]

Salary​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1/1/2018​

[/td][td]John[/td][td]Smith[/td][td]

102​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1/2/2018​

[/td][td]Jack[/td][td]Jones[/td][td]

98​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1/3/2018​

[/td][td]Jill[/td][td]Smith[/td][td]

52​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1/4/2018​

[/td][td]Tom[/td][td]Jones[/td][td]

69​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1/5/2018​

[/td][td]Tom[/td][td]Smith[/td][td]

12​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1/6/2018​

[/td][td]John[/td][td]Smith[/td][td]

103​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1/6/2018​

[/td][td]Tony[/td][td]Jones[/td][td]

54​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1/6/2018​

[/td][td]Jill[/td][td]Smith[/td][td]

270​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1/7/2018​

[/td][td]Jill[/td][td]Jones[/td][td]

103​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1/7/2018​

[/td][td]Jack[/td][td]Jones[/td][td]

120​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1/8/2018​

[/td][td]Jill[/td][td]Jones[/td][td]

78​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

1/9/2018​

[/td][td]Tony[/td][td]Smith[/td][td]

83​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1/10/2018​

[/td][td]Tom[/td][td]Jones[/td][td]

47​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

1/6/2018​

[/td][td]Jack[/td][td]Jones[/td][td="bgcolor:#CCFFCC"]

98​

[/td][td]D17: =LOOKUP(9E+307, $D$2:$D$14/((A17>=$A$2:$A$14)*(B17=$B$2:$B$14)*(C17=$C$2:$C$14)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

1/7/2018​

[/td][td]Jack[/td][td]Jones[/td][td="bgcolor:#CCFFCC"]

120​

[/td][td][/td][/tr]
[/table]

 

[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 ?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Date​

[/TD]
[TD="bgcolor: #F3F3F3"]

FirstName​

[/TD]
[TD="bgcolor: #F3F3F3"]

Surname​

[/TD]
[TD="bgcolor: #F3F3F3"]

Salary​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

1/1/2018​

[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]

102​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

1/2/2018​

[/TD]
[TD]Jack[/TD]
[TD]Jones[/TD]
[TD]

98​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

1/3/2018​

[/TD]
[TD]Jill[/TD]
[TD]Smith[/TD]
[TD]

52​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

1/4/2018​

[/TD]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]

69​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

1/5/2018​

[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

1/6/2018​

[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

8​

[/TD]
[TD]

1/6/2018​

[/TD]
[TD]Tony[/TD]
[TD]Jones[/TD]
[TD]

54​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

9​

[/TD]
[TD]

1/6/2018​

[/TD]
[TD]Jill[/TD]
[TD]Smith[/TD]
[TD]

270​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

10​

[/TD]
[TD]

1/7/2018​

[/TD]
[TD]Jill[/TD]
[TD]Jones[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

11​

[/TD]
[TD]

1/7/2018​

[/TD]
[TD]Jack[/TD]
[TD]Jones[/TD]
[TD]

120​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

12​

[/TD]
[TD]

1/8/2018​

[/TD]
[TD]Jill[/TD]
[TD]Jones[/TD]
[TD]

78​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

13​

[/TD]
[TD]

1/9/2018​

[/TD]
[TD]Tony[/TD]
[TD]Smith[/TD]
[TD]

83​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

14​

[/TD]
[TD]

1/10/2018​

[/TD]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]

47​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

17​

[/TD]
[TD]

1/6/2018​

[/TD]
[TD]Jack[/TD]
[TD]Jones[/TD]
[TD="bgcolor: #CCFFCC"]

98​

[/TD]
[TD]D17: =LOOKUP(9E+307, $D$2:$D$14/((A17>=$A$2:$A$14)*(B17=$B$2:$B$14)*(C17=$C$2:$C$14)))[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

18​

[/TD]
[TD]

1/7/2018​

[/TD]
[TD]Jack[/TD]
[TD]Jones[/TD]
[TD="bgcolor: #CCFFCC"]

120​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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