|
Home > Formulas > Lookup > 30 Functions 30 Excel Functions in 30 Days 30XL30DHow 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 isn't decided yet, but should include 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 will be in Excel 2010/2007 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 on the Contextures website too. For example, you can watch one of these videos on MATCH or VLOOKUP:
Excel MATCH Function Video Tutorial
Excel VLOOKUP Function Video Tutorial
Hide Excel VLOOKUP Function Errors With IF and ISNA
Or watch this HYPERLINK video.
More Function Tutorials
Last updated: January 26, 2023 4:08 PM