I have 2 columns of File # data, representing different weeks in a payroll cycle. I also have 2 columns of Regular Hours data. I am using VLOOKUP and SUM to add the Regular Hours together to receive hours for the pay period.
=SUM(VLOOKUP($AI2,RICS_TimeClocks!O$2:S,4, FALSE) , VLOOKUP($AI2,RICS_TimeClocks!T$2:X,4, FALSE))
I have the File #s and Names flattened into one column each with
=UNIQUE(FLATTEN(
The issue I have now though, is that there are employees that only worked on one of the weeks, resulting in a
"Did not find value ‘____’ in VLOOKUP evaluation"…
Any suggestions so that the formula can function when there is information in only one of 2 data columns? Example, File # 43021 only works in the second week, and File # 43034 only works in the first week, but I still want to be able to compute and display their total hours.
…or a better way to match and add the information into another flattened column of information?
>Solution :
try IFNA set to zero:
=SUM(IFNA(VLOOKUP($AI2, RICS_TimeClocks!O$2:S, 4, 0), 0),
IFNA(VLOOKUP($AI2, RICS_TimeClocks!T$2:X, 4, 0), 0))