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.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Worked[/TD]
[/TR]
[TR]
[TD]16-Mar-2018[/TD]
[TD]John[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tablename: RATECARD​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rate[/TD]
[TD]Rate Type[/TD]
[TD]Resource[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]80[/TD]
[TD]Hourly[/TD]
[TD]Engineer[/TD]
[/TR]
</tbody>[/TABLE]


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.