Contextures Blog

Yesterday, in the 30XL30D challenge, we found text strings with the SEARCH function, and used IFERROR and ISNUMBER to deal with its error results.

MATCH Function

For day 19 in the challenge, we’ll examine the MATCH function. It searches for a value in an array, and returns its position, if the value is found.

To see a the MATCH function sample workbook, you can watch this short video. There are written steps below the video

Function 19: MATCH

The MATCH function returns the position of a value in an array, or #N/A if not found. The array can be sorted, or unsorted, and the MATCH function is not case sensitive.

How Could You Use MATCH?

The MATCH function returns the position of an item in an array, and that result can be used by other functions, such as INDEX or VLOOKUP. For example:

• Find position of item in unsorted list
• Use with CHOOSE to get student grades
• Use with VLOOKUP for flexible column choice
• Use with INDEX for to show winner’s name

MATCH Syntax

The MATCH function has the following syntax:

• MATCH(lookup_value,lookup_array,[match_type])
  • lookup_value can be text, number or logical value
  • lookup_array is an array, or array reference (contiguous cells in a single row or column)
  • match_type can be -1, 0 or 1. If omitted, assumed to be 1

MATCH Traps

MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.

Example 1: Find Item in Unsorted List

For an unsorted list, you can use 0 as the match_type argument, to find an exact match. If you’re searching for text, and using 0, you can include wildcard characters in the lookup value.

In this example, you can type a month name, or partial name with wildcards, to find that month’s position in the list.

=MATCH(D2,B3:B7,0)

Instead of an array reference, you can enter an array as the lookup_array argument.

In this variation, the lookup month name is entered in cell D5, and three month names are entered in the MATCH function’s second argument.

If a later month, such as Oct, is entered in D5, the result will be #N/A.

=IF(C2+0<14,”Time to upgrade”,”Latest version”)

Example 2: Change Student Grades to Letters

Just as you did with VLOOKUP, you can use MATCH to help convert a student’s score to a letter grade. In this example, it is combined with CHOOSE, to get the letter grade. The match_type is -1, because the scores are sorted in descending order.

When the match_type is -1, the result is the smallest value greater than or equal to lookup value. The lookup value is 54, and it’s not in the list of scores, so the position for 60 is returned.

Because 60 is in position 4, the 4th value in the CHOOSE options is the result — cell C6, with a value of D.

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

Example 3: Create Flexible Column Choice in VLOOKUP

To make a VLOOKUP formula more flexible, you can use MATCH to find a column number, instead of hard-coding it into the formula.

In this example, users can select a region in cell H1, as the value for the VLOOKUP. Then, they can select a Month in cell H2, and the MATCH function returns the column for that month.

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

Example 4: FIND Closest Match with INDEX

The MATCH function also works well with the INDEX function, which we’ll see later in the challenge.

In this example, the MATCH function is used to find the guess that is closest to the correct number.

1. The ABS function returns the absolute difference between each guess and the correct number.
2. The MIN function finds the smallest difference.
3. The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned.
4. The INDEX function returns the name in that position in the list of names.

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

Download the MATCH Function File

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

Watch the MATCH Video

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

_____________

Yesterday, in the 30XL30D challenge, we found values with the LOOKUP function, and we’ll use that function again today, when working with error results.

ERROR.TYPE Function

For day 17 in the challenge, we’ll examine the ERROR.TYPE function. It can identify specific types of errors, and you can use that information to help with troubleshooting.

So, let’s take a look at the ERROR.TYPE information and examples, and if you have other tips or examples, please share them in the comments.

Watch the ERROR.TYPE Video

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

Function 17: ERROR.TYPE

The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.

How Could You Use ERROR.TYPE?

With the ERROR.TYPE function, you can:

• identify an error type
• help users troubleshoot error results

ERROR.TYPE Syntax

The ERROR.TYPE function has the following syntax:

• ERROR.TYPE(error_val)
  • error_val is the error that you want to identify
  • ERROR.TYPE codes:
    • 1…..#NULL!
    • 2…..#DIV/0!
    • 3…..#VALUE!
    • 4…..#REF!
    • 5…..#NAME?
    • 6…..#NUM!
    • 7…..#N/A
    • #N/A..Other

ERROR.TYPE Traps

If the error_val is not an error, the result of the ERROR.TYPE function is an #N/A error. You can avoid this, by using ISERROR to test for an error, as shown in Example 2.

Example 1: Identify the Error Type

With the ERROR.TYPE function you can check a cell, to identify which error it contains.

If there isn’t an error in the cell, the result is #N/A, instead of an error type code number.

=ERROR.TYPE(B3)

In this example, cell B3 contains #VALUE!, so the error type is 3.

Example 2: Help Users Troubleshoot Errors

By combining the ERROR.TYPE function with other functions, you can help users troubleshoot error results in a cell.

In this example, numbers should be entered in cells B3 and C3.

• If text is entered, the result in D3 is a #VALUE! error.
• If a zero is entered in cell C3, the result is a #DIV/0! error.

In cell D4, ISERROR checks for an error, and the ERROR.TYPE function returns a number for the error. The LOOKUP function finds the applicable troubleshooting message from a table of error type codes, and displays it.

=IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3), $B$9:$B$15, $D$9:$D$15),””)

Here’s the lookup table, with Error Type numbers, and messages.

Download the ERROR.TYPE Function File

To see the formulas used in today’s examples, you can go to the Excel Formula Error Values page on my Contextures site.

On that page, go to the Download section, to get the ERROR.TYPE function sample workbook. The file is zipped, and is in xlsx file format, with no macros.

_____________

Yesterday, in the 30XL30D challenge, we got cell details with the CELL function, and learned that it’s useful for a few things, like extracting a worksheet name.

COLUMNS Function

For day 12 in the challenge, we’ll examine the COLUMNS function. Will this function be as useful? Or is it just another lazy function, like AREAS? Well, it does count the columns, as promised, and plays well with others, but nothing too exotic or powerful here.

So, let’s take a look at the COLUMNS information and examples, and if you have other tips or examples, please share them in the comments.

Function 12: COLUMNS

The COLUMNS function returns the number of columns in an array or reference.

How Could You Use COLUMNS?

The COLUMNS function can show the size of a table or named range:

• Count columns in an Excel Table
• Sum last column in a named range

COLUMNS Syntax

The COLUMNS function has the following syntax:

• COLUMNS(array)
  • array is an array or array formula, or reference to a range.

COLUMNS Traps

If you’re using a range reference, it must be a contiguous range.

Example 1: Count Columns in an Excel Table

In Excel 2007 and Excel 2010, you can create a formatted Excel table, and refer to its name in a formula. In this example, there is a table named RegionSales,

and the COLUMNS function counts the number of columns in that table.

=COLUMNS(RegionSales)

Example 2: Sum Last Column in Named Range

If you combine the COLUMNS function with SUM and INDEX, you can get the total for the last column in a named range. Here, the range is named MyRange,

and this formula sums the last column in the named range.

=SUM(INDEX(MyRange,,COLUMNS(MyRange)))

Download the COLUMNS Function File

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

Watch the COLUMNS Video

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

_____________

For day 10 in the 30XL30D challenge, we’ll examine the HLOOKUP function. Not too surprisingly, this Microsoft Excel function is very similar to VLOOKUP, and works with items that are in a Horizontal list.

Less Popular Than VLOOKUP

Poor HLOOKUP isn’t as popular as its sibling, VLOOKUP,  though, because most tables are set up with the lookup values listed vertically.

When was the last time that you wanted to do a horizontal lookup?

Do you ever need to search for a value horizontally, across a row, and then return a value from that column, in a specific row below?

Anyway, let’s give HLOOKUP its moment in the spotlight, and take a look at its information and examples.

Remember, if you have other tips or examples, please share them in the comments.

Function 10: HLOOKUP

The HLOOKUP function looks for a value horizontally, in the first row of a table, and then it returns another value from a different row, in the same column, in that table.

How Could You Use HLOOKUP?

The HLOOKUP function can either find exact matches in the lookup row, or it can find the closest match. For example, an HLOOKUP formula can:

• Find the sales total in a selected region
• Find rate in effect on selected date

HLOOKUP Syntax

The HLOOKUP function has the following syntax (sequence of arguments):

• HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
  • lookup_value: the value that you want to look for in the first row of the lookup range — it can either 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 in the range. Be sure that the lookup values are in the top row or a table.
  • row_index_num: the row that has the value you want returned, based on the row number within the table. NOTE: This can be different from the worksheetrow number.
  • [range_lookup]: for an exact match, use FALSE or 0 in this argument; for an approximate match, use TRUE or 1, with the lookup value row sorted horizontally, in ascending order.

HLOOKUP Traps

Like VLOOKUP, the HLOOKUP function can be slow, especially if you are 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 row, in ascending order, and use an approximate match, instead of an exact match.

Tip: You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table’s first row. This can make the calculation much faster in some situations.

HLOOKUP Alternatives

Other Excel functions, such as INDEX and MATCH, can be used to return values from a table, instead of HLOOKUP, and those functions are more efficient.

We will look at the INDEX and MATCH functions later in the 30 Day challenge, and you will see examples of how flexible and powerful those functions are.

Tip: See examples for INDEX and MATCH on my Contextures site: INDEX and MATCH Functions

Also, see these pages on my site, for examples of related lookup functions:

Compare Lookup Functions

VLOOKUP Function

Example 1: Find the Sales for a Selected Region

The HLOOKUP function always looks for a value in the top row of the lookup table.

In this example, we will find the sales total for a selected region — East, West, North or South. Those region names are in the first row of the lookup range, and they are NOT sorted in ascending order.

For our financial report, it is important to get the correct sales amount as our formula result, so the following settings the HLOOKUP function will find an exact match.

Worksheet Setup – Example 1

Here are the details on the Excel worksheet setup for this example. You can see the worksheet in the screen shot below:

• a region name is entered in cell B7
• the region lookup table has two rows, and is in range C2:F3
• sales total is in row 2 of the table.
• FALSE is used in the last argument, to find an exact match for the lookup value.

The HLOOKUP Formula

The formula in cell C7 is:

• =HLOOKUP(B7,C2:F3,2,FALSE)

HLOOKUP Error Result

Sometimes, you might get an error resulet with an HLOOKUP formula, as you can see in the screen shot below.

In this case, the region name, “Central”, was not found in the first row of the lookup table, so the HLOOKUP formula result is an error value —  #N/A

Example 2: Find Rate for Selected Date

In most cases, an exact match is required when using the HLOOKUP function,  but sometimes an approximate match works better.

For example, if rates change at the start of each quarter, only those quarterly dates are entered as column headings. It would not be practical to enter every single date in the heading row!

Instead of trying to find an exact match for the date, with HLOOKUP and an approximate match, you can find the rate that went into effect on the closest date.

Worksheet Setup Notes

Here are the details on the worksheet setup for Example 2. You can see the worksheet in the screen shot below:

In this example:

• a date is entered in cell C5
• the rate lookup table has two rows, and is in range C2:F3
• the lookup table is sorted by the Date row, in ascending order
• dates are in the top row of a table
• rate is in row 2 of the table.
• TRUE is used in the last argument, so HLOOKUP will find an approximate match for the lookup value.

HLOOKUP Formula Example 2

The formula in cell D5 is:

• =HLOOKUP(C5,C2:F3,2,TRUE)

How Approximate Match Works

Here is how the approximate match works, in this example:

• If the exact lookup date is not found in the first row of the table, so the HLOOKUP formula finds the next largest value that is less than lookup_value.
• The lookup value in this example is March 15th.
• That value is not in the date row, so the HLOOKUP function returns the rate for January 1st (0.25).
• January 1st is the largest date in the heading row, that is LESS than the lookup date of March 15th

More HLOOKUP Examples

For more HLOOKUP examples, and for tips on fixing HLOOKUP formula problems, go to this page on my Contextures site:

How to Use Excel HLOOKUP Function and Fix Problems

On that page, you’ll see how to:

• use HLOOKUP with the COUNTIF function, for faster calculations
• create an HLOOKUP formula for 2 criteria
• interpret any HLOOKUP error results – #VALUE! and #REF! and #N/A
• fix problems when looking up dates or numbers
• and much more!

Download the HLOOKUP Function File

To see both of the formulas used in today’s 30 Functions in 30 Days challenge examples, you can download the HLOOKUP function sample workbook.

The file is zipped, and is in Excel’s xlsx file format. The workbook does not contain any macros.

Tip: Use this workbook to follow along with the HLOOKUP video, in the next section.

Watch the HLOOKUP Video

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

_______________________________