Search code examples
arraysgoogle-sheetssumgoogle-sheets-formulavlookup

SUM of 2 columns from VLOOKUP in Google Sheets when the search key is not in both columns

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?

ScreenShot of Google Sheet

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