SOLVED

# Excel - Select a rate from a table using Multiple criteria

Occasional Contributor

# 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

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

# Re: Excel - Select a rate from a table using Multiple criteria

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.

# Re: Excel - Select a rate from a table using Multiple criteria

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]