snowynight

xlookup: now that my office adopted windows365, xlookup has replaced vlookup as my mostly used function for search and data reference. Compared to vlookup, xlookup needs less variable to function, can return a data range instead of a single result, and uses exact match as default. It can search for data both horizontally and vertically, in addition to the capability to refer to to the left of the lookup_value. It also supports an optional parameter called not_found which overrides the #N/A error in case of no result found.

Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

text: I often have to convert date in the original data to a tidy mm/dd format, and text can let me do that.

Syntax for date conversion: =TEXT(value,"M/D")

choose: Sometimes I only need to return a value based on a very short list, such as sex/gender. Then I will use choose instead of xlookup/vlookup.

Syntax: CHOOSE (index_num, value1, [value2], ...)

countif: It's very useful when I need to count the number of cells that meet a criterion for statistics. COUNTIFS is its more powerful sibling that can evaluate different criteria in the same or different ranges, but usually countif is good enough for me.

Syntax: =COUNTIF(range, criteria)

right: Sometimes I needs to extract part of a text string for use. Right function returns the last character or characters in a text string, based on the number of characters you specify

Syntax: RIGHT(text,[num_chars])