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)

Hi @jonh54 


in Microsoft 365, you could use the FILTER function to get the proper rate.




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