Home > Formulas > Lookup > 30 Functions

30 Excel Functions in 30 Days 30XL30D

How to use Excel's Text, Information and Lookup functions, such as VLOOKUP, INDEX, INDIRECT and CELL


Introduction to the 30XL30D Challenge

Do you want to learn more about Excel functions? Even the functions that you use every day can have hidden talents, and pitfalls that you aren't aware of. And there are so many functions, that you probably only use a fraction of them.

In January 2011, for the 30XL30D challenge, we focused on 30 Excel functions in the following categories:

  • Text
  • Information
  • Lookup and Reference

Follow the Contextures Blog

Every day, from January 2, 2011 to January 31, 2011, there was an article on the Contextures Blog, featuring one of the Excel functions. You can read the articles there, and watch the daily Excel Function video.

The Contextures Blog readers voted for the functions they wanted to learn about, and final list of functions included a few favourites, like:

To see how the Excel functions work, you can download the 30XL30D sample files, from the daily Contextures Blog articles. The files are in Excel xlsx format, and zipped.

Excel Functions Challenge Daily Blog Posts

Day 01 - EXACT -- check for an exact match between text strings, including upper and lower case.

Day 02 - AREAS -- returns the number of areas in a reference

Day 03 - TRIM -- removes extra spaces from text string

Day 04 - INFO -- get information about the operating environment

Day 05 - CHOOSE -- get a value from a list, based on an index number

Day 06 - FIXED -- round a number and return the result as text.

Day 07 - CODE -- returns a numeric code for the first character in a text string

Day 08 - CHAR -- returns a specified character, for the number entered

Day 09 - VLOOKUP -- looks for a value in first column of table, returns another value from same row

Day 10 - HLOOKUP -- looks for a value in first row of table, returns another value from same column

Day 11 - CELL -- gives info about cell formatting, contents and location

Day 12 - COLUMNS -- returns the number of columns in an array or reference

Day 13 - TRANSPOSE -- changing vertical ranges to horizontal ones, or vice versa

Day 14 - T -- returns the text from a value, or an empty string for no text

Day 15 - REPT -- repeats a text string a specified number of times

Day 16 - LOOKUP -- returns a value from a one-row or one-column range or from an array

Day 17 - ERROR.TYPE -- returns a number based on error type

Day 18 - SEARCH -- looks for a text string, within another text string, and returns its position

Day 19 - MATCH -- searches for value in array, and returns its position, if found

Day 20 - ADDRESS -- creates address as text, from row and column numbers

Day 21 - TYPE -- identifies the type of value in a cell, by returning a number

Day 22 - N -- Returns number based on a cell's value

Day 23 - FIND -- finds text within another string, and is case sensitive

Day 24 - INDEX -- returns value or reference, based on row and column number

Day 25 - REPLACE -- replaces a specified number of characters in a text string, with new text.

Day 26 - OFFSET -- returns reference, of specified size, offset from starting reference

Day 27 - SUBSTITUTE -- replaces old text with new text, in a text string, and is case sensitive

Day 28 - HYPERLINK -- creates hyperlink in a cell, based on link location and friendly name

Day 29 - CLEAN -- removes non-printing characters from text

Day 30 - INDIRECT -- returns the reference specified by a text string

Watch the Function Videos

To see the functions in action, you can watch the short video tutorial that is posted in each blog article.

There are other videos here on the Contextures website too. For example, you can watch this HYPERLINK video.

More Function Tutorials

SUM Function

VLOOKUP Function


Count Functions


Last updated: April 14, 2024 3:40 PM