SUM if criteria met, lookup another table

[trejjy]

Hi all,

I've been struggling with a formula that I am trying to get to work and want to ask if anyone can assist. I have a workbook with named ranges and a named table.

The named ranges are called, DATE, NAME, and WORKED. The table is called RATECARD and has headers NAME, RATE, RESOURCE, RATE TYPE.

I want to sum the hours worked for a resource based on their role. In this case I am seeking to total the hours of an ENGINEER. The formula would look at the NAME ranged, look up the table called RATECARD for the matching name, and the corresponding RATECARD[RESOURCE] if they were an engineer then I would be returned the total amount of hours.

I havent been able to get EXACT match working when using the VLOOKUP parameter and think I need some help.

Date	Name	Worked
16-Mar-2018	John	3.00

<tbody>
</tbody>

Tablename: RATECARD​

Name	Rate	Rate Type	Resource
John	80	Hourly	Engineer

<tbody>
</tbody>

Thanks in advance.

Trej

 

[njimack]

Shouldn't there be a named range called RESOURCE?

 

[trejjy]

Hi njimack,

I am importing a CSV file in to the sheet which has the location of the named ranges, the data set from the CSV file doesnt have a RESOURCE column so I am using the name to reference the RATECARD table for the additional information. I am able to make it work for the RATECARD[RATE] field, but only because the VLOOKUP equation is working on the TRUE switch, this needs the RATECARD table to be sorted for the RATECARD[NAME] field. When I use the same equation to lookup the RATECARD[RESOURCE] parameter it doesnt work because the RATECARD needs to be sorted using the RATECARD[RESOURCE] field.

Thanks for helping, but trying the make the workbook as simple as possible.