Simplify the Formula

0

I need to make this "complicated" to a single formula

=IF($H$1="ivanov",LOOKUP(2,1/($B$2:$B$5<=I2),$C$2:$C$5),IF($H$1="petrova",LOOKUP(2,1/($B$6:$B$9<=I2),$C$6:$C$9),"none"))

Please see attached.

Answer
Discuss

Answers

0

Please try this formula in cell J2 of your worksheet & copy down.

=AGGREGATE(14,2,(Empl=H$1)*(Dates<=$I2)*Salary,1)

For the above formula to work you need 3 named ranges as follows.

A2:A9 = "Empl"
B2:B9 = "Dates"
C2:C9 = "Salary"

Note that "Date" isn't a good name for a range because there is a worksheet function by the same name.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login