Forum Discussion

Copper Contributor

Sep 12, 2022

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

excel

office 365

Martin_Weiss
Sep 12, 2022
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.

Martin_Weiss

Bronze Contributor

Sep 12, 2022

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.

jonh54

Copper Contributor

Sep 28, 2022

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]