Forum Discussion
Solved
Excel - Select a rate from a table using Multiple criteria
I have created two tables
Table1 Contains current and past rates with Client No StartDate Enddate & Rate.
Table2 Contains ClientNo and job details. + the calculation of (JobTime X Ratecharged )at the time of Invoice. eg; 5 hrs on 8/8/22; Table1 Client 5, StDate 1st Aug 22, EndDate 31st Jul 23 Rate $50.
Having trouble with this calculation. 5 x 50 $250
I need to be able to select From Table1 the rate, with a ClientId = InvId, InvDate >= StartDate, InvDate <=Endate . Any Ideas & or Help would be much appreciated. jh
Hi jonh54
in Microsoft 365, you could use the FILTER function to get the proper rate.
=FILTER($D$4:$D$12,($A$4:$A$12=G4)*($B$4:$B$12<=H4)*($C$4:$C$12>=H4))
Note:
In my example I assume, that there will always be just a single match for the lookup-criteria. Otherwise, the formula in J4 will spill into the next cells.
2 Replies
Hi jonh54
in Microsoft 365, you could use the FILTER function to get the proper rate.
=FILTER($D$4:$D$12,($A$4:$A$12=G4)*($B$4:$B$12<=H4)*($C$4:$C$12>=H4))
Note:
In my example I assume, that there will always be just a single match for the lookup-criteria. Otherwise, the formula in J4 will spill into the next cells.- Thanks; work perfectly.. Sorry for the late reply have'nt been able to work due to get back to it due to family illness. Thank again I was able to finish the project. jh
=(FILTER(tbl_PayRates[Rate],(tbl_PayRates[ClientID]=[@ClientID])*(tbl_PayRates[StDate]<=[@DateWorked])*(tbl_PayRates[Edate]>=[@DateWorked])))*[@HrsWorked]
