Google Sheets Formula for Lookup

Objective

Create a formula that looks up a person’s working hours on a specific day in Google Sheets.

Assumptions

• You have a table where:
  • Column A contains names of individuals.
  • Column B contains dates.
  • Column C contains working hours.

Sample Data Layout

Google Sheets Formula

To find the working hours for a specific person on a specific date, use the following formula:

=INDEX(C:C, MATCH(1, (A:A="John Doe") * (B:B=DATE(2023,10,1)), 0))

Explanation of the Formula

1. INDEX(C:C, ...): This part retrieves the value from column C, which contains the working hours.

2. MATCH(1, ...): This function finds the row number that matches the criteria defined.

3. (A:A="John Doe"): This checks if the name in column A equals "John Doe". This returns an array of TRUE/FALSE.

4. (B:B=DATE(2023,10,1)): This checks if the date in column B equals October 1, 2023. This also returns an array of TRUE/FALSE.

5. Multiplication operator (*): This combines both conditions. It only returns 1 (TRUE) for rows where both conditions are TRUE, all other combinations result in 0 (FALSE).

6. 0: The argument in MATCH specifies to find the exact match.

Practical Example

Implementation

1. Place the names, dates, and hours in columns A, B, and C respectively.
2. Choose an empty cell where you want to display the result.
3. Input the formula with the specifics adjusted for the name and date you wish to look up.

Output

If executed with "John Doe" and the date October 1, 2023, the result would yield 8 since that is the number of hours John worked on that day.

Conclusion

This formula effectively allows users to look up working hours based on a person’s name and a specific date from a data set in Google Sheets. For further practice, consider exploring courses on the Enterprise DNA Platform that offer advanced data analysis techniques.