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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Simplify the Formula
0
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.