Lookup and Date Functions in Google Sheets

Introduction

Google Sheets offers powerful tools for managing and analyzing data, including lookup and date functions. This article explores essential lookup functions such as VLOOKUP, HLOOKUP, INDEX, and MATCH, as well as date functions like DATE, YEAR, DAY, MONTH, TODAY(), DATEDIF(), EDATE(), and EOMONTH(). By mastering these functions, you can streamline data management tasks and gain deeper insights.

Importance of Lookup and Date Functions

Lookup functions help retrieve specific data from large datasets, making it easier to organize and analyze information. Date functions aid in handling date and time data, performing calculations, and automating date-based tasks. Both are crucial for maintaining accurate and useful data.

VLOOKUP Function

The VLOOKUP (Vertical Lookup) function searches for a value in the first column of a range and returns a value from a specified column in the same row.

How to Use the VLOOKUP Function

You can use VLOOKUP to find product prices, retrieve employee information, or match student scores.

• Syntax: =VLOOKUP(search_key, range, index, [is_sorted])
• Example: =VLOOKUP(A1, B1, 3, FALSE)

Syntax

=VLOOKUP(search_key, range, index, [is_sorted])

Inputs

1. search_key: The value to search for in the first column of the range.
2. range: The upper and lower values to consider for the search.
3. index: The index of the column with the return value of the range. The index must be a positive integer.
4. is_sorted: Optional input. Choose an option:
   • FALSE = Exact match. This is recommended.
   • TRUE = Approximate match. This is the default if is_sorted is unspecified.
     Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you are likely to get a wrong return value. Learn why you may encounter a wrong return value.

HLOOKUP Function

The HLOOKUP (Horizontal Lookup) function searches for a value in the first row of a range and returns a value from a specified column in the same row.

How to Use the HLOOKUP Function

HLOOKUP is useful for retrieving data organized horizontally, such as sales data for specific months or course codes.

• Syntax: =HLOOKUP(search_key, range, row, [is_sorted]).
• Example: =HLOOKUP(A1, A1, 5, FALSE)

Syntax

HLOOKUP(search_key, range, index, [is_sorted])

• search_key – The value to search for. For example, 42, ‘Cats’ or I24.
• range – Consider the range for the search, searching the first row for the specified search_key.
• index – The index specifies the row number of the value to return, with the first row in the range numbered 1.
  • If index is not between 1 and the number of rows in range, #VALUE! is returned.
• is_sorted – [OPTIONAL – TRUE by default] – Indicates whether the row to be searched (the first row of the specified range) is sorted.
  • If you set is_sorted to TRUE or omit it, the function returns the nearest match (less than or equal to the search key). If all values in the search row are greater than the search key, it returns #N/A.
  • If you set is_sorted to TRUE or omit it and the first row of the range isn’t sorted, the function might return an incorrect value.
  • If you set is_sorted to FALSE, the function returns only an exact match. If there are multiple matching values, it returns the content of the cell corresponding to the first value found. If no such value is found, it returns #N/A.

Notes

• Ensure you do not sort the first row in the range using text values when searching for numeric or date values. For example, numbers should be correctly ordered as (1, 2, 10, 100) rather than (1, 10, 100, 2) if they were sorted as strings. Incorrect sorting methods can lead to the function returning inaccurate results.
• Search keys based on regular expressions or wildcard patterns are NOT supported. Use QUERY instead.
• HLOOKUP has much better performance with sorted ranges and is_sorted set to TRUE. Consider sorting the row being searched.

INDEX and MATCH Functions

The INDEX and MATCH functions combine to provide a flexible and powerful lookup tool. INDEX returns the value of a cell in a specified range based on row and column numbers, while MATCH searches for a value in a range and returns its position.

INDEX Function : INDEX returns the content of a cell specified by row and column offsets.

How to Use the INDEX Function

• Syntax: =INDEX(range, row, [column])
• Example: =INDEX(A1, 5, 2)

Returns the cell(s) found by index value into the referenced range.

Syntax

INDEX(reference, [row], [column])

• reference – The reference is the range of cells from which the values are returned.
• row – [OPTIONAL – 0 by default] – The row, optional and defaulted to 0, specifies the index of the row to return within the reference range of cells.
• column – [OPTIONAL – 0 by default] – The column, optional and defaulted to 0, specifies the index of the column to return within the reference range of cells.

MATCH

MATCH returns the relative position of an item in a range that matches a specified value.

How to Use the MATCH Function

• Syntax: =MATCH(search_key, range, [match_type])
• Example: =MATCH(A1, B1, 0)

Syntax

MATCH(search_key, range, [search_type])

• search_key – The value to search for. For example, 42, ‘Cats’ or I24.
• range – The one-dimensional array to be searched.
  • If a range with both height and width greater than 1 is used, MATCH will return #N/A!.
• search_type – [ OPTIONAL – 1 by default ] – The manner in which to search.
  • 1, the default, causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key.
  • 0 indicates exact match and is required in situations where range is not sorted.
  • -1 causes MATCH to assume that the range is sorted in descending order and return the smallest value greater than or equal to search_key.

Combining INDEX and MATCH

INDEX and MATCH can be used together to perform more advanced and dynamic lookups.

• Example: =INDEX(B1:B10, MATCH(A1, A1:A10, 0))

Tip: VLOOKUP can be used when the lookup value is to the left of the desired attribute to return. INDEX and MATCH can be used regardless of where the lookup value is located in the dataset.

INDEX and MATCH combine, enabling dynamic lookups, especially useful when the search column isn’t the first or when data isn’t sorted.

See also

MATCH: Returns the relative position of an item in a range that matches a specified value.

OFFSET: Returns a range reference that shifts a specified number of rows and columns from a starting cell reference.

Notes: If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively.

Date Function

Date functions help you work with dates in Google Sheets, allowing you to create, extract, and manipulate date values.

DATE Functions

The DATE function creates a date value from individual year, month, and day components.

• Syntax: =DATE(year, month, day)
• Example: =DATE(2023, 7, 15)

YEAR, MONTH, and DAY Functions

Year

Returns the year specified by a given date.

Sample usage

• YEAR(DATE(1969,7,20))
• YEAR(A2)
• YEAR(40909)

Syntax

YEAR(date)

date – The date from which to calculate the year. Must be a cell reference to a cell containing a date, a function returning a date type or a number.

TODAY() Function

The TODAY() function returns the current date.

• Syntax: =TODAY()
• Example: =TODAY()

DATEDIF() Function

Calculates the number of days, months or years between two dates.

Sample usage

• DATEDIF(DATE(1969, 7, 16), DATE(1969, 7, 24), “D”)
• DATEDIF(A1, A2, “YM”)
• DATEDIF(“7/16/1969”, “7/24/1969”, “Y”)

Syntax

DATEDIF(start_date, end_date, unit)

start_date – the start date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type or a number.

end_date – the end date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type or a number.

unit – a text abbreviation for unit of time. For example,‘M’ for month. Accepted values are ‘Y’,‘M’,‘D’,‘MD’,‘YM’,‘YD’.

‘Y’: the number of whole years between start_date and end_date.

‘M’: the number of whole months between start_date and end_date.

‘D’: the number of days between start_date and end_date.

‘MD’: the number of days between start_date and end_date after subtracting whole months.

‘YM’: the number of whole months between start_date and end_date after subtracting whole years.

‘YD’: the number of days between start_date and end_date, assuming start_date and end_date were no more than one year apart.

EDATE() Function

The EDATE() function returns the date that is a specified number of months before or after a given date.

• Syntax: =EDATE(start_date, months)
• Example: =EDATE(A1, 3)

EOMONTH() Function

The EOMONTH() function returns the last day of the month, either before or after a given date by a specified number of months.

Sample usage

• EOMONTH(DATE(1969, 7, 20), 1)
• EOMONTH(A2, 1)
• EOMONTH(40909, -2)

Syntax

EOMONTH(start_date, months)

start_date – The date from which to calculate the the result.

months – The function calculates the number of months before (negative) or after (positive) start_date to consider, returning the last calendar day of the calculated month.

These functions help automate date calculations, manage deadlines, and track project timelines.

Conclusion

Lookup and date functions are essential tools in Google Sheets for managing and analysing data. By mastering functions like VLOOKUP, HLOOKUP, INDEX, MATCH, DATE, YEAR, MONTH, DAY, TODAY(), DATEDIF(), EDATE(), and EOMONTH(), along with effectively implementing data validation, you can ensure that your data is accurate, consistent, and well-structured.

Frequently Asked Questions