SOLVED

Excel - Select a rate from a table using Multiple criteria

Occasional Contributor

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

2 Replies
best response confirmed by jonh54 (Occasional Contributor)
Solution

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))

Martin_Weiss_0-1662990604630.png

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]