Contextures Blog

For day 9 in the 30XL30D challenge, we’ll examine the VLOOKUP function. As you can guess by its name, this is one of the Lookup functions, and works with items that are in a Vertical list.

Other functions might do a better job of pulling data from a table (see VLOOKUP traps section below), but VLOOKUP is the lookup function that people try first.

Some people get the hang of it right away, and others struggle to make it work. Yes, this function has some flaws, but once you understand how it works, you’ll be ready to move on to some of the other lookup options.

So, let’s take a look at the VLOOKUP information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!

Function 09: VLOOKUP

The VLOOKUP function looks for a value in the first column in a table, and returns another value from the same row in that table.

How Could You Use VLOOKUP?

The VLOOKUP function can find exact matches in the lookup column, or the closest match, so it can:

• Find the price of a selected product
• Convert student percentages to letter grades

VLOOKUP Syntax

The VLOOKUP function has the following syntax:

• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
  • lookup_value: the value that you want to look for — it can be a value, or a cell reference.
  • table_array: the lookup table — this can be a range reference or a range name, with 2 or more columns.
  • col_index_num: the column that has the value you want returned, based on the column number within the table.
  • [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value column sorted in ascending order..

VLOOKUP Traps

VLOOKUP can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first column, in ascending order, and use an approximate match.

You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table’s first column (see example 3 below).

Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient. We’ll look at those functions later in the challenge, and see how flexible and powerful they are.

Example 1: Find the Price for a Selected Item

The VLOOKUP function looks for a value in the left column of the lookup table.

In this example, we’ll find the price for a selected product. It’s important to get the correct price, so the following settings are used:

• a product name is entered in cell B7
• the pricing lookup table has two columns, and is in range B3:C5
• price is in column 2 of the table.
• FALSE is used in the last argument, to find an exact match for the lookup value.

The formula in cell C7 is:

=VLOOKUP(B7,B3:C5,2,FALSE)

If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is #N/A

Example 2: Convert Percentages to Letter Grades

Usually, an exact match is required when using VLOOKUP, but sometimes an approximate match works better. For example, when converting student percentages to letter grades, you wouldn’t want to type every possible percentage in the lookup table.

Instead, you could enter the lowest percentage for each letter grade, and then use VLOOKUP with an approximate match. In this example:

• a percentage is entered in cell C9
• the percentage lookup table has two columns, and is in range C3:D7
• the lookup table is sorted by the percentage column, in ascending order
• letter grade is in column 2 of the table.
• TRUE is used in the last argument, to find an approximate match for the lookup value.

The formula in cell D9 is:

=VLOOKUP(C9,C3:D7,2,TRUE)

If the percentage is not found in the first column of the lookup table, the VLOOKUP formula result is the next largest value that is less than lookup_value.

The lookup value in this example is 77. That value is not in the percentage column, so the value for 75 (B) is returned.

Example 3: Find Exact Price With Approximate Match

The VLOOKUP function can be slow when doing an exact match for a text string.

In this example, we’ll find the price for a selected product, without using the Exact Match setting. To prevent incorrect results:

• the lookup table is sorted by the first column, in ascending order
• COUNTIF checks for the value, to prevent incorrect results

The formula in cell C7 is:

=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)

If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is 0.

Download the VLOOKUP Function File

To see the formulas used in today’s examples, you can download the VLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.

More VLOOKUP Info and Examples

• For more VLOOKUP examples, you can visit the VLOOKUP page on the Contextures website.
• Check out Chandoo’s blog, where he had an entire VLOOKUP week, with tips and examples.
• For suggestions on speeding up a lookup formula, see Charles Williams’ page on Optimizing Lookups.

Watch the VLOOKUP Videos

To see a demonstration of the examples in the VLOOKUP function sample workbook, you can watch these 2 short Excel video tutorials.

Find Product Price with VLOOKUP

Convert Percentages to Letter Grades

_____________

Congratulations! You’ve made it to the first weekend in the 30XL30D challenge, including yesterday’s investigation of the FIXED function.

CODE Function

We’ll take it easy today, and look at a function that doesn’t have too many examples — the CODE function. It can work with other functions, in long, complicated formulas, but today we’ll focus on what it can do on its own, and in simple formulas.

So, let’s take a look at the CODE information and examples, and if you have other tips or examples, please share them in the comments. Wear your secret deCODEr ring, if you have one.

Function 07: CODE

The CODE function returns a numeric code for the first character in a text string. For Windows, the returned code is from the ANSI character set, and for Macintosh, the code is from the Macintosh character set.

How Could You Use CODE?

The CODE function can help unravel mysteries in your data, such as:

• What hidden character is at the end of imported text?
• How can I type a special symbol in a cell?

CODE Syntax

The CODE function has the following syntax:

• CODE(text)
  • text is the text string from which you want the first character’s code

CODE Traps

Results could be different if you switch to a different operating system. The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard.

However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here:

Differences between ANSI, ISO-8859-1 and MacRoman character sets

For example, the ANSI code 189 is ½ and for the Macintosh it is O

Example 1: Get Hidden Character’s Code

When you copy text from a website, it might include hidden characters. The CODE function can be used to identify what those hidden characters are.

For example, there is a text string in cell B3, and only “test” is visible — 4 characters. In cell C3, the LEN function shows that there are 5 characters in cell B3.

To identify the last character’s code, you can use the RIGHT function, to return the last character. Then, use the CODE function to return the code for that character.

=CODE(RIGHT(B3,1))

In cell D3, the RIGHT/CODE formula shows that the last character has the code 160, which is a non-breaking space used on websites.

Example 2: Find a Symbol’s Code

To insert special characters in an Excel worksheet, you can use the Symbol command on the Ribbon’s Insert tab. For example, you can insert a degree symbol ° or a copyright symbol ©.

After you insert a symbol, you can determine its code, by using the CODE function

=IF(C3=””,””,CODE(RIGHT(C3,1)))

Once you know the code, you can use the numeric keypad (not the regular numbers) to insert the symbol. The code for the copyright symbol is 169. Follow these steps to enter that symbol in a cell.

On a keyboard with a numeric keypad

1. On the keyboard, press the Alt key
2. On the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
3. Press Enter, to see the copyright symbol in the cell.

On a keyboard with no numeric keypad

On your laptop, you might need to press special keys to use the numeric keypad function. Check the owner’s manual, for directions. Here’s what works on my Dell laptop.

1. Press the Fn key, and the F4 key, to enable NumLock
2. Locate the numeric keypad within the letters on the keyboard. On my keyboard, J=1, K=2, etc.
3. Press the Alt key, and the Fn key, and using the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
4. Press Enter, to see the copyright symbol in the cell.
5. When finished, press the Fn key, and the F4 key, to disable NumLock

Download the CODE Function File

To see the formulas used in today’s examples, you can download the CODE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the CODE Video

To see a demonstration of the examples in the CODE function sample workbook, you can watch this short Excel video tutorial.

_____________

Yesterday, in the 30XL30D challenge, we cleaned things up with the TRIM function, and learned that it’s no SUBSTITUTE for calorie counting.

INFO Function

For day 4 in the challenge, we’ll examine the INFO function. Excel Help warns us to be careful with this function, or we could reveal private information to other users.

So, let’s take a look at the INFO information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!

Function 04: INFO

The INFO function shows information about the current operating environment.

How Could You Use INFO?

The INFO function can show information about the Excel application, such as:

• Microsoft Excel version
• Number of active worksheets
• Current recalculation mode

In previous versions of Excel you could also get memory information (“memavail”, “memused”, and “totmem”), but those type_text items are no longer supported.

INFO Syntax

The INFO function has the following syntax:

• INFO(type_text)
  • type_text is one of the following items, that specifies what information you want.
  • “directory” Path of current directory
  • “numfile” Number of active worksheets in open workbooks.
  • “origin” Absolute cell reference of top left visible
  • “osversion” Current operating system version, as text.
  • “recalc” Current recalculation mode; “Automatic” or “Manual”.
  • “release” Microsoft Excel version, as text.
  • “system” Name of the operating environment: “pcdos” or “mac”

INFO Traps

In Excel’s Help file, there is a warning that you should use the INFO function with caution, because it could reveal confidential information to other users.

For example, you might not want other people to see the file path that your Excel workbook is in. If you’re sending an Excel file to someone else, be sure to remove any data that you don’t want to share!

Example 1: Microsoft Excel version

With the “release” value, you can use the INFO function to show what version of Excel is being used. The result is text, not a number.

In the screenshot below, Excel 2010 is being used, so the version number is 14.0.

=INFO(“release”)

You could use the result to display a message, based on version number.
=IF(C2+0<14,”Time to upgrade”,”Latest version”)

Example 2: Number of active worksheets

With the “numfile” type_text value, the INFO function can show the number of active worksheets in all open workbooks. This number includes hidden sheets, sheets in hidden workbooks, and sheets in add-ins.

In this example, an add-in is running, and it has two worksheets, and the visible workbook has five worksheets. The total sheets returned by the INFO function is seven.

=INFO(“numfile”)

Example 3: Current recalculation mode

Instead of typing the type_text value as a string in the INFO function, you can refer to a cell that contains one of the valid values.

In this example, there is a data validation drop down list in cell B3, and the INFO function refers to it.

=INFO(B3)

When “recalc” is selected, the result shows that the current recalculation mode is Automatic.

Download the INFO Function File

To see the formulas used in today’s examples, you can download the INFO function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the INFO Video

To see a demonstration of the examples in the INFO function sample workbook, you can watch this short Excel video tutorial.

_____________

Yesterday, in the 30XL30D challenge, we took a poke at the lazy brother-in-law function — AREAS. It’s not used much in the working world, but you saw how the 3 reference operators work, so I hope that was useful!

TRIM Function

For day 3 in the challenge, we’ll examine the TRIM function. In January, some people are trying to TRIM a few pounds, and my Excel Calorie Counter and Excel Weight Loss Tracker workbooks are popular.

Unfortunately, the TRIM function won’t help with the pounds, but can remove extra spaces from a text string.

So, let’s take a look at the TRIM information and examples, and if you have other tips or examples, please share them in the comments. And good luck with the calorie counting!

Function 03: TRIM

The TRIM function removes all the spaces in a text string, except for single spaces between words.

How Could You Use TRIM?

The TRIM function can help with the cleanup of text that you’ve downloaded from a website, or imported from another application. The TRIM function:

• Removes spaces from start and end of text string
• Removes all except single spaces between words
• Does NOT remove some special space characters copied from websites

TRIM Syntax

The TRIM function has the following syntax:

• TRIM(text)
  • text is a cell reference or text string from which you want spaces removed.

TRIM Traps

The TRIM function only removes standard space characters from the text. If you copy text from a website, it might contain special non-breaking space characters, and the TRIM function will not remove those.

Example 1: Removes spaces from start and end of text string

You can use the TRIM function to remove all the space characters at the start and end of a text string. In the screenshot below, there are 2 extra spaces at the start and 2 at the end of the text in cell C5.

The TRIM function in cell C7 removes those 4 spaces.

=TRIM(C5)

Example 2: Removes all except single spaces between words

You can use the TRIM function to extra space characters between words in a text string. In the screenshot below, there are 3 extra spaces between the words in cell C5.

The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.

=TRIM(C5)

Example 3: Does NOT remove some space characters

The TRIM function does NOT remove some space characters, such as a non-breaking space copied from a website. In the screenshot below, Cell C5 contains one non-breaking space, and that is not trimmed.

=TRIM(C5)

You can manually delete the non-breaking space character, or use the SUBSTITUTE function or a macro. You’ll see other ways to clean up your data during the 30 Excel Functions in 30 Days challenge.

Download the TRIM Function File

To see the formulas used in today’s examples, you can download the TRIM function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the TRIM Video

To see a demonstration of the examples in the TRIM function sample workbook, you can watch this short Excel video tutorial.

_____________

Yesterday we started the 30XL30D challenge with the action-packed, fun-filled, EXACT function. It had several examples and ways to apply it in your own workbooks.

AREAS Function

Today we’ll investigate the AREAS function, and it’s a bit lighter in the usefulness department (to put it politely). It’s like the lazy brother-in-law, in a bad sitcom, who lies on your couch, and drinks your beer.

But, even a layabout can have a purpose. That lazy brother-in-law can serve as an example for your children, of how NOT to behave. As for the AREAS function, we can use it to see how the 3 different reference operators work, and how they affect the formula results.

So, let’s take a look at the AREAS information and examples, and if you have other examples, please share them in the comments. But don’t send me your brother-in-law!

Function 02: AREAS

The AREAS function returns the number of areas in a reference — an area is a range of contiguous cells or a single cell. The cells can be empty, or contain data – that has no effect on the count.

How Could You Use AREAS?

The AREAS function doesn’t have many real-world uses, but it’s an interesting example of how the reference operators work. You can use the AREAS function to do the following:

• Count the number of areas in a range
• Count the number of intersections for multiple ranges
• Calculate an area number for an INDEX function

AREAS Syntax

The AREAS function has the following syntax:

• AREAS(reference)
  • reference can be a single cell or range, or can refer to multiple areas.

Reference Operators

When entering references, you can use any of the 3 reference operators:

:	colon	A1:B4	Range	all cells between, and including, the two references
,	comma	A1, B2	Union	combine multiple references into one
	space	A1 B3	Intersection	cells common to the references

AREAS Traps

If you are using a comma in the AREAS function, to refer to multiple ranges or cells, add another set of parentheses.

=AREAS((F2,G2:H2))

Otherwise, the comma will be interpreted as a field separator, and you’ll get a “too many arguments” error.

Example 1: Count the Areas in a Range

You can use the AREAS function with a simple range reference, and the count will be 1.

=AREAS(G2:H2)

Example 2: Count the Areas in Multiple References

You can use the AREAS function with multiple references, to get a total count of areas. Because a comma is used as the union operator, you’ll need to add an extra set of parentheses in the formula.

=AREAS((F2,G2:H2))

The two ranges in the reference are adjacent, but are counted as separate areas, so the formula result is 2.

Example 3: Count the Areas in Overlapping References

Even if the references overlap, or one reference is completely within another, when using the comma as the union operator, each area will be counted separately.

=AREAS((F2,F2:H2))

The two references overlap, and F2 is completely within the F2:H2 range, but they are counted as separate areas, so the formula result is 2.

Example 4: Count the Areas in Intersecting References

When you use the space character to create an intersection from the references, the intersection areas will be counted.

=AREAS(TESTREF01 TESTREF02)

The named range TESTREF01 is coloured blue and TESTREF02 is coloured purple. These ranges intersect at three points, outlined with bold borders, so the formula result is 3.

Example 5: Calculate the Area Number for INDEX

The INDEX function, in Reference form, can use area number as its final argument.

INDEX(reference,row_num,column_num,area_num)

This example, based on an Excel newsgroup post by Leo Heuser, refers to a non-contiguous named range – TestBlock.

In the INDEX formula, TestBlock is the reference, and the AREAS function calculates the number of areas in the TestBlock range.

To get the value from TestBlock, row 5, column 1, last area, use this formula:

=INDEX(TestBlock,5,1,AREAS(TestBlock))

The last area is Day04, and the 5th value in Day04 is H05, which is the formula result.

Download the AREAS Function File

To see the formulas used in today’s demo, you can download the AREAS function sample workbook. The file is zipped, and is in Excel 2007 file format.

Try to use each function in your own workbooks. Then, for extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you’ll remember it better.

Watch the AREAS Video

To see a demonstration of the examples in the AREAS function sample workbook, you can watch this Excel video tutorial.

_____________