Excel Function Friday: HLOOKUP Finds Current Price

iconlookup2 On Day 10 of the 30 Excel Functions in 30 Days series, we looked at the Excel HLOOKUP function. It’s similar to VLOOKUP, but looks for values in a horizontal list, instead of a vertical list.
The second example in that HLOOKUP blog post showed how to find a rate in a lookup table, based on the date entered in cell C5. On March 15th, the rate would be 0.25, because the Jan 1st rate is still in effect.
Hlookup03

Beyond One Cell

In the comments for the HLOOKUP blog post, Fred said that he got the formula working correctly in cell D5, but wondered how to use the result in multiple cells.
In this example, we’ll use the rates as a lookup for pricing. The prices change quarterly, and the correct price will be used in each order, based on the order date.
HLookupRates03

Set Up the Lookup Table

In this workbook, the table with the quarterly dates and rates is on a separate sheet, named Rates. New rates will be added each quarter, so we’ll create a dynamic range named RateTable, using the technique from Example 3 in the 30XL30D INDEX function post.
In this HLOOKUP rates table, the formula for the named range is:
=Rates!$A$1:INDEX(Rates!$2:$2,1,COUNT(Rates!$2:$2))
HLookupRates01

Create the HLOOKUP Formula

In the Orders table, we’ll use an Excel HLOOKUP formula to pull the correct rate from the RateTable range, based on the order date.
In cell B2, the formula is:
=HLOOKUP(A2,RateTable,2)
The final argument is omitted, so the result is an approximate match. If the order date isn’t found in the first row of the RateTable range, the HLOOKUP formula result is based on the next largest date that is less than order date.
HLookupRates02

Add the Pricing Formula

The final step is to add the pricing formula in column D. Quantities will be entered in column C, so the pricing formula will multiply the quantity by the rate.
The formula in cell D2 is:
=B2*C2
HLookupRates03

Download the Sample File

To see the Excel HLOOKUP formula and the RateTable named range, you can download the HLOOKUP Rates sample file. It is in Excel 2007 format, and zipped.
_______________

30 Excel Functions in 30 Days: Conclusion

Icon30DayThanks for participating in the 30XL30D challenge, that ended yesterday. My favourite Excel function in the series was INDEX — I learned a few useful tricks while researching that one! How about you?
As I promised, here is a list of the 30 functions, with their rank in your voting.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

30 Excel Functions Ranking

  1. VLOOKUP
  2. ADDRESS
  3. INDIRECT
  4. INDEX
  5. OFFSET
  6. MATCH
  7. CHOOSE
  8. AREAS
  9. LOOKUP
  10. CELL
  11. FIND
  12. HLOOKUP
  13. TRANSPOSE
  14. ERROR.TYPE
  15. EXACT
  16. SUBSTITUTE
  17. CLEAN
  18. REPLACE
  19. SEARCH
  20. CODE
  21. T
  22. INFO
  23. N
  24. COLUMNS
  25. HYPERLINK
  26. TYPE
  27. TRIM
  28. CHAR
  29. FIXED
  30. REPT

Buy the 30XL30D eBook Kit

If you want all of the 30XL30D content in an easy-to-use single reference file, you can buy the 30 Excel Functions in 30 Days eBook kit. Learn more about the 30 Excel functions in:

  • a 150 page written lesson guide
  • links to all the Excel video tutorials
  • an Excel file with 100 function examples

For more details, please go to the 30 Excel Functions in 30 Days eBook kit page on the Contextures website.
30 Excel Functions in 30 Days
Save

30 Excel Functions in 30 Days: 30 – INDIRECT

Icon30DayCongratulations! You made it to the final day in the 30XL30D challenge. It’s been an long, and interesting, journey, and I hope you learned a few useful things about Excel functions along the way. Tomorrow, I’ll do a wrap-up article, and let you know how the functions ranked in the pre-challenge voting, last month.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
For day 30, we’ll examine the INDIRECT function, which returns the reference specified by a text string. It’s one of the ways that you can create a dependent data validation drop down list, where, for example, the selection in the Country drop down controls the choices in the City drop down.
So, let’s take a look at the INDIRECT information and examples, and if you have other tips or examples, please share them in the comments.

Function 30: INDIRECT

The INDIRECT function returns the reference specified by a text string.
Indirect00

How Could You Use INDIRECT?

The INDIRECT function returns the reference specified by a text string, so you can use it to:

  • Create starting reference that doesn’t shift
  • Create reference to static named range
  • Create reference from sheet, row, column info
  • Create array of numbers that doesn’t shift

INDIRECT Syntax

The INDIRECT function has the following syntax:

  • INDIRECT(ref_text,a1)
    • ref_text is the text string for a reference.
    • a1 if TRUE or omitted, uses an A1 reference style; if FALSE, the R1C1 reference style is used

INDIRECT Traps

  • The INDIRECT function is volatile, so it could slow down your workbook, if used in many formulas.
  • If the INDIRECT function creates a reference to another workbook, that workbook must be open, or the formula will result in a #REF! error.
  • If the INDIRECT function creates a reference to a range outside the row and column limit, the formula will result in a #REF! error. (Excel 2007 and Excel 2010)
  • The INDIRECT function cannot resolve a reference to a dynamic named range

Example 1: Create starting reference that doesn’t shift

In the first example, there are identical numbers in columns C and E, and the totals are the same, using the SUM function. However, the formulas are slightly different. In cell C8, the formula is:
=SUM(C2:C7)
In cell E8, the INDIRECT function creates a reference to the starting cell, E2:
=SUM(INDIRECT(“E2”):E7)
Indirect01a
If a row is inserted at the top of the lists, and January amounts are entered, the total in column C doesn’t change. The formula changed, adjusting to the inserted row:
=SUM(C3:C8)
However, the INDIRECT function locked the starting cell to E2, so the January amount is automatically included in the column E total. The ending cell changed, but the starting cell wasn’t affected.
=SUM(INDIRECT(“E2”):E8)
Indirect01b

Example 2: Create reference to static named range

The INDIRECT function can also create a reference for a named range. In this example, the blue cells are in a range named NumList. There is also a dynamic range in column B, based on the count of numbers in that column.
The total for either range can be calculated, by using the range name with the SUM function, as you can see in cells E3 and E4
=SUM(NumList)  or =SUM(NumListDyn)
Indirect02a
Instead of typing the name in the SUM formula, you can refer to the range name in a worksheet cell. For example, with the name NumList in cell D7, the formula in cell E7 is:
=SUM(INDIRECT(D7))
Unfortunately, the INDIRECT function can’t resolve a dynamic range, so when the formula is copied down to cell E8, the result is a #REF! error.
Indirect02b

Example 3: Create reference from sheet, row, column info

You can easily create a reference based on row and column numbers, by using FALSE as the second argument in the INDIRECT function. This creates an R1C1 style reference, and in this example, a sheet name is also included — ‘MyLinks’!R2C2
=INDIRECT(“‘” & B3 & “‘!R” & C3 & “C” & D3,FALSE)
Indirect03

Example 4: Create array of numbers that doesn’t shift

In some formulas, you need an array of numbers, as in this example, where we want the average of the 3 highest numbers in column B. The numbers could be typed in the formula, as they are in cell D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
If you need a bigger array of numbers, you probably wouldn’t want to type all of them. Another option is to use the ROW function, as in the array-entered formula in cell D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
A third option is to use the ROW function with INDIRECT, as in the formula in cell D6, which is also array-entered:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT(“1:3”))))
The results for all 3 formulas are the same.
Indirect04a
However, if rows are inserted at the top of the sheet, the second formula returns an incorrect result, because the rows are adjusted. Now, instead of the average of the top 3 numbers, it shows the average of the 3rd, 4th and 5th largest numbers.
With the INDIRECT function, the third formula keeps the correct row reference, and continues to show the correct result.
Indirect04b

Download the INDIRECT Function File

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

Watch the INDIRECT Video

To see a demonstration of the examples in the INDIRECT function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Excel INDIRECT Function Gets Reference from Text String

_____________

30 Excel Functions in 30 Days: 29 – CLEAN

Icon30DayYesterday, in the 30XL30D challenge, we jumped around a workbook, and opened Excel files and websites, by using the HYPERLINK function.
For day 29 in the challenge, we’ll examine the CLEAN function. Sometimes the data you get from a website, or in a download file, has some unwanted characters, and the CLEAN function can help you fix it. It doesn’t do much heavy lifting though, and refuses to help with the mess that the kids make. This will be perfect for a lazy Sunday!
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the CLEAN information and examples, and if you have other tips or examples, please share them in the comments.

Function 29: CLEAN

The CLEAN function shows removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157.
Clean00

How Could You Use CLEAN?

The CLEAN function can remove some non-printing characters from text , but not all of them. You can use CLEAN, or other functions when necessary, to:

  • Remove some non-printing characters
  • Replace non-printing characters in text

CLEAN Syntax

The CLEAN function has the following syntax:

  • CLEAN(text)
    • text is any information from which you want the non-printing characters removed

CLEAN Traps

The CLEAN function only removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157. For other non-printing characters, such as the non-breaking space character 160, you can use SUBSTITUTE to replace them with space characters, or empty strings.

Example 1: Remove non-printing characters

The CLEAN function works to remove some non-printing characters, such as those in the 0-30 range of the ASCII character set. In this example, I added characters 9 and 13 to the original text string from C3.
=CHAR(9) & C3 & CHAR(13)
The LEN function shows that the number of characters in cell C5 increased to 15, with those non-printing characters included.
Clean01a
With the CLEAN function, in cell C7, those characters are removed, and the number of characters is reduced by 2, so it’s back to the original 13 characters.
=CLEAN(C5)
Clean01b

Example 2: Replace non-printing characters

For the characters that the CLEAN function can’t remove, like characters 127 and 160, you can use the SUBSTITUTE function to replace them.
=SUBSTITUTE(E3,CHAR(C3),””)
Clean02b

Download the CLEAN Function File

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

Watch the CLEAN Video

To see a demonstration of the examples in the CLEAN function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Excel CLEAN Function Removes Non-Printing Characters

_____________

30 Excel Functions in 30 Days: 28 – HYPERLINK

Icon30DayYesterday, in the 30XL30D challenge, we replaced text with the SUBSTITUTE function, and used it to create flexible reports.
For day 28 in the challenge, we’ll examine the HYPERLINK function. Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the HYPERLINK information and examples, and if you have other tips or examples, please share them in the comments.

Function 28: HYPERLINK

The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet.
Hyperlink00

How Could You Use HYPERLINK?

The HYPERLINK function can open a document, or jump to a specific location, so you can:

  • Link to location in same file
  • Link to Excel file in same folder
  • Link to website

HYPERLINK Syntax

The HYPERLINK function has the following syntax:

  • HYPERLINK(link_location,friendly_name)
    • link_location is the text string for the location where you want to go.
    • friendly_name is the text you want displayed in the cell

HYPERLINK Traps

If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel’s Ribbon. That should show you the correct syntax, then recreate that in your link_location argument.

Example 1: Link to location in same file

There are several different ways to create the text string for the link_location argument. In the first example, the ADDRESS function returns the address for row 1, column 1, on the sheet that is named in cell B3.
The pound sign (#) at the start of the address indicates that the location is within the current file.
=HYPERLINK(“#”&ADDRESS(1,1,,,B3),D3)
Hyperlink01a
You could also use the & operator to construct the link location. Here, the sheet name is in cell B5 and the cell is in C5.
=HYPERLINK(“#”&”‘” & B5 & “‘!” & C5,D5)
Hyperlink01b
For a link to a named range in the same workbook, just use the range name as the link location.
=HYPERLINK(“#”&D7,D7)
Hyperlink01c

Example 2: Link to Excel file in same folder

To create a link to another Excel file, in the same folder, just use the file name as the link_location argument for the HYPERLINK function.
For files that are up a level or more in the hierarchy, use two periods and a backslash for each level.
=HYPERLINK(C3,D3)
Hyperlink02

Example 3: Link to a website

You can also link to website pages with the HYPERLINK function. In this example, the URL is constructed from text strings, and the website name is used as the friendly name.
=HYPERLINK(“http://www.” &B3 & “.com”,B3)
Hyperlink03

Download the HYPERLINK Function File

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

Watch the HYPERLINK Video

To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Open Files with Excel HYPERLINK Function

_____________

30 Excel Functions in 30 Days: 27 – SUBSTITUTE

Icon30DayYesterday, in the 30XL30D challenge, we used the OFFSET function to return a reference, and saw that it is similar to the non-volatile INDEX function.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
For day 27 in the challenge, we’ll examine the SUBSTITUTE function. Like the REPLACE function, it replaces old text with new text, in a text string, but can replace multiple instances of the same text.
In some situations though, it’s quicker and easier to use the Find/Replace command on the Excel Ribbon, with Match Case option turned on, for case sensitive replacement.
So, let’s take a look at the SUBSTITUTE information and examples, and if you have other tips or examples, please share them in the comments.

Function 27: SUBSTITUTE

The SUBSTITUTE function replaces old text with new text, in a text string. It will replace all instances of the old text, unless a specific occurrence is selected, and SUBSTITUTE is case sensitive.
Substitute00

How Could You Use SUBSTITUTE?

The SUBSTITUTE function replaces old text with new text, in a text string, so you could use it to:

  • Change region name in report title
  • Remove non-printing characters
  • Replace last space character

SUBSTITUTE Syntax

The SUBSTITUTE function has the following syntax:

  • SUBSTITUTE(text,old_text,new_text,instance_num)
    • text is the text string or cell reference, where text will be replaced.
    • old_text is the text that will be removed
    • new_text is the text that will be added
    • instance_number is the specific occurrence of old text that you want replaced

SUBSTITUTE Traps

  • The SUBSTITUTE function can replace all instances of the old text, so use the instance_num argument if you want only a specific occurrence of old text replaced.
  • For replacements that are not case sensitive, you can use the REPLACE function.

Example 1: Change region name in report title

With the SUBSTITUTE function, you can create a report title that changes automatically, based on the region name that is selected. In this example, the report title is entered in cell C11, which is named RptTitle. The “yyy” in the title text will be replaced with the region name, selected in cell D13.
=SUBSTITUTE(RptTitle,”yyy”,D13)
Substitute01

Example 2: Remove non-printing characters

When you copy data from a website, there might be hidden, non-printing space characters in the text. If you try to remove space characters from the text in Excel, the TRIM function can’t remove them. The characters aren’t normal space characters (character 32); they are non-breaking space characters (character 160).
Instead, you can use the SUBSTITUTE function to replace each of the non-printing spaces with a normal space character. Then, use TRIM to remove all the extra spaces.
=TRIM(SUBSTITUTE(B3,CHAR(160),” “))
Substitute02

Example 3: Replace last space character

Instead of replacing all instances of a text string, you can use the SUBSTITUTE function’s instance_number argument to select a specific instance. In this list of recipe ingredients, we want to replace the last space character only.
In cell C3, the LEN function calculates the number of characters in cell B3. The SUBSTITUTE function replaces all the space characters with empty strings, and the second LEN function finds the length of the revised string. The length is 2 characters shorter, so there are two spaces.
=LEN(B3)-LEN(SUBSTITUTE(B3,” “,””))
Substitute03a
In cell D3, the SUBSTITUTE function replaces only the 2nd space character with the new text –  ” | ”
=SUBSTITUTE(B3,” “,” | “,C3)
Instead of using two columns for this formula, you could combine them into one long formula.
=SUBSTITUTE(B3,” “,” | “,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))
Substitute03b

Download the SUBSTITUTE Function File

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

Watch the SUBSTITUTE Video

To see a demonstration of the examples in the SUBSTITUTE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Change Text with Excel SUBSTITUTE Function

_____________

30 Excel Functions in 30 Days: 26 – OFFSET

Icon30DayYesterday, in the 30XL30D challenge, we changed text strings with the REPLACE function, and learned that it can insert characters too.
For day 26 in the challenge, we’ll examine the OFFSET function. From a starting reference, it returns another reference, of a specified size, and offset by a set number of rows and columns.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the OFFSET information and examples, and if you have other tips or examples, please share them in the comments.

Function 26: OFFSET

The OFFSET function returns a reference offset from a given reference.
Offset00

How Could You Use OFFSET?

The OFFSET function can return a reference to a range, and be combined with other functions. Use it to:

  • Find sales amount for selected month
  • Sum the selected month’s sales
  • Create a dynamic range based on count
  • Sum the last n month’s sales

OFFSET Syntax

The OFFSET function has the following syntax:

  • OFFSET(reference,rows,cols,height,width)
    • reference is a cell or range of adjacent cells.
    • rows can be positive (below starting reference) or negative (above starting reference)
    • cols can be positive (right of starting reference) or negative (left of starting reference)
    • height must be positive, and is number of rows in returned reference
    • width must be positive, and is number of columns in returned reference
    • if height or width are omitted, starting reference size is used

OFFSET Traps

The OFFSET function is volatile, so it could slow down a workbook if used in too many cells. Instead, you could use another function, like INDEX, to return a reference.

Example 1: Find sales amount for selected month

With the OFFSET function, you can return a reference to a range, based on a starting reference. In this example, we want the sales amount in cell G2:

  • starting reference is cell C1
  • number of rows to offset is entered in cell F2
  • sales amount is in column C, so the number of columns to offset is zero
  • height is 1 row
  • width is 1 column

=OFFSET(C1,F2,0,1,1)
In cell H2, there is a similar OFFSET formula, to return the month name. The only difference is the column offset — 1 instead of zero.
=OFFSET(C1,F2,1,1,1)
Note: The height and width arguments could be omitted, because we want a reference that is the same size as the starting reference. I used them in this example, to show how all the arguments work.
Offset01

Example 2: Sum the selected month’s sales

In this example, the OFFSET function returns a reference to the sales amounts for the selected month, and the SUM function returns the total for that range. In cell B10, the selected month number is 3, so the result is the total of the March sales.

  • starting reference is A3:A6
  • rows to offset is zero (you could omit the zero, for the same result)
  • columns to offset is entered in cell B10
  • height and width are omitted, because the reference should be the same size as the starting reference

=SUM(OFFSET(A3:A6,0,B10))
Offset02

Example 3: Create a dynamic range based on count

You can also use the OFFSET function to create a dynamic range. In this example, I’ve created a name, MonthsList, with this formula:
=OFFSET(‘Ex03’!$C$1,0,0,COUNTA(‘Ex03’!$C:$C),1)
Offset03a
If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthsList as its source.
Offset03b

Example 4: Sum the last n month’s sales

In this final example, OFFSET is combined with SUM and COUNT, to show the total for the last n months. As new quantities are added, the formula result will automatically adjust to include the latest months. In cell E2, the number of months is 2, so the August and September amounts are summed.

  • starting reference is cell C2
  • number of rows to offset is calculated by counting numbers in column C, subtracting number in cell E3, adding 1
  • quantity is in column C, so the number of columns to offset is zero
  • height is entered in cell E3
  • width is 1 column

=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))
Offset04

Download the OFFSET Function File

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

Watch the OFFSET Video

To see a demonstration of the examples in the OFFSET function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Excel OFFSET Function Gets Range from Row and Column

_____________

30 Excel Functions in 30 Days: 25 – REPLACE

Icon30DayYesterday, in the 30XL30D challenge, we used the INDEX function to return a value or reference, based on a row and/or column number.
For day 25 in the challenge, we’ll examine the REPLACE function, which is in the Text category. It replaces a specified number of characters in a text string, with new text.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the REPLACE information and examples, and if you have other tips or examples, please share them in the comments.

Function 25: REPLACE

The REPLACE function replaces characters within text, based on the number of characters, and starting position, specified.
Replace00

How Could You Use REPLACE?

The REPLACE function can replace characters in a text string, such as:

  • change area code in phone number
  • Replace first space with colon and space
  • Use nested REPLACE to insert hyphens

REPLACE Syntax

The REPLACE function has the following syntax:

  • REPLACE(old_text,start_num,num_chars,new_text)
    • old_text is the text string in which characters will be replaced.
    • start_num is the position of the old characters
    • num_chars is the number of old characters that will be replaced
    • new_text is the text that will replace the original text

REPLACE Traps

The REPLACE function replaces a specified number of characters at the indicated starting position. To replace a specific text string, anywhere in the original text, you can use the SUBSTITUTE function, which we’ll see later in the challenge.

Example 1: Change area code in phone number

With the REPLACE function, you can change the first three digits in a phone number, when a new area code is introduced. In this example, the new area code is entered in column C, and the revised phone numbers are shown in column D.
=REPLACE(B3,1,3,C3)
Replace01

Example 2: Replace first space with colon and space

To identify the starting position for the REPLACE function, you can use the FIND function, to locate a specific text string or character. In this example, we want to replace the first space character with a colon and space character.
=REPLACE(B3,FIND(” “,B3,1),1,”: “)
Replace02

Example 3: Use nested REPLACE to insert hyphens

The REPLACE function can be nested, so multiple replacements are made in the old text string. In this example, the list of phone numbers needs to have hyphens inserted, after the first 3 numbers, and after the second 3 numbers. By using zero as the number of characters to replace, none of the numbers will be removed, and hyphens will be inserted.
=REPLACE(REPLACE(B3,4,0,”-“),8,0,”-“)
Replace03

Download the REPLACE Function File

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

Watch the REPLACE Video

To see a demonstration of the examples in the REPLACE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Change Text with Excel REPLACE Function

_____________

30 Excel Functions in 30 Days: 24 – INDEX

Icon30DayYesterday, in the 30XL30D challenge, we found text strings with the FIND function, and learned that it is case sensitive, unlike the SEARCH function.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
For day 24 in the challenge, we’ll examine the INDEX function. Based on a row and column number, it can return a value or reference to a value. We’ve already used INDEX several times, with other functions in the challenge:

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

Function 24: INDEX

The INDEX function returns a value or reference to a value. Combine it with other functions, like MATCH, for powerful formulas.
Index00

How Could You Use INDEX?

The INDEX function can return a value or reference to a value, so you can use it to:

  • Find sales amount for selected month
  • Get reference to specified row, column, area
  • Create a dynamic range based on count
  • Sort column of text in alphabetical order

INDEX Syntax

The INDEX function has two syntax forms — Array and Reference. With Array form, a value is returned, and with Reference form, a reference is returned.
The Array form has the following syntax:

  • INDEX(array,row_num,column_num)
    • array is an array constant or range of cells
    • if array has only 1 row or column, corresponding row/column number argument is optional
    • if array has >1 row or column, and only row_num or column_num is used, array of entire row or column is returned
    • row_num – if omitted, column_num is required
    • column_num – if omitted, row_num is required
    • if both the row_num and column_num arguments are used, returns value in cell at intersection of row_num and column_num
    • if row_num or column_num are zero, returns array of values for entire column or row

The Reference form has the following syntax:

  • INDEX(reference,row_num,column_num,area_num)
    • reference can refer to one or more cell ranges – enclose nonadjacent ranges in parantheses
    • if each area in reference has only 1 row or column, corresponding row/column number argument is optional
    • area_num selects range in reference from which to return row and column intersection
    • area_num  – if omitted, area 1 is used
    • if row_num or column_num are zero, returns reference for entire column or row
    • result is a reference, and can be used by other functions

INDEX Traps

If the row_num and column_num don’t point to a cell within the array or reference, the INDEX function returns a #REF! error.

Example 1: Find sales amount for selected month

Enter a row number, and the INDEX function returns the sales amount from that row in the reference. Here the month number is 4, so the April sales amount is returned.
=INDEX($C$2:$C$8,F2)
Index01a
To make the formula more flexible, you could use MATCH to return the row number, based on the month that was selected from a drop down list.
=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))
Index01b

Example 2: Get reference to specified row, column, area

In this example, there is a named range, MonthAmts, which consists of 3 non-contiguous ranges. The MonthAmts range has 3 areas — one for each month — and there are 4 rows and 2 columns in each area. Here is the formula for the MonthAmts name:
=’Ex02′!$B$3:$C$6,’Ex02′!$E$3:$F$6,’Ex02′!$H$3:$I$6
With the INDEX function, you can return the cost or revenue amount for a specific region and month.
=INDEX(MonthAmts,B10,C10,D10)
Index02a
The INDEX function result can be multiplied, as in the Tax calculation in cell F10:
=0.05*INDEX(MonthAmts,B10,C10,D10)
or, it can return a reference for the CELL function, to show the address of the result, in cell G10.
=CELL(“address”,INDEX(MonthAmts,B10,C10,D10))
Index02b

Example 3: Create a dynamic range based on count

You can also use the INDEX function to create a dynamic range. In this example, I’ve created a name, MonthList, with this formula:
=’Ex03′!$C$1:INDEX(‘Ex03’!$C:$C,COUNTA(‘Ex03’!$C:$C))

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthList as its source.
Index03a

Example 4: Sort column of text in alphabetical order

In the final example, the INDEX function is combined with several other functions, to return a list of months, sorted in alphabetical order. The COUNTIF function shows how many month names come before a specific month name. SMALL returns the nth smallest item in the list, and MATCH returns the row number for that month.
In the video, you can see the formula broken down into steps.
This formula is array-entered, by pressing Ctrl + Shift + Enter.
=INDEX($C$4:$C$9,MATCH(SMALL(
COUNTIF($C$4:$C$9,”<“&$C$4:$C$9),ROW(E4)-ROW(E$3)),
COUNTIF($C$4:$C$9,”<“&$C$4:$C$9),0))

Index04

Download the INDEX Function File

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

Watch the INDEX Video

To see a demonstration of the examples in the INDEX function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Excel INDEX Function Gets Value from Row and Column

_____________

30 Excel Functions in 30 Days: 23 – FIND

Icon30DayYesterday, in the 30XL30D challenge, we had a light day, with the N function, and learned that can return a number, based on a value.
For day 23 in the challenge, we’ll examine the FIND function. It’s similar to the SEARCH function, which we saw on Day 18, but the FIND function is case sensitive.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the FIND information and examples, and if you have other tips or examples, please share them in the comments.

Function 23: FIND

The FIND function finds a text string, within another text string, and is case sensitive.
Find00

How Could You Use FIND?

The FIND function can find text within another string, and is case sensitive. For example:

  • Find start position of text in text string
  • Find exact values from a list
  • Find street name in address

FIND Syntax

The FIND function has the following syntax:

  • FIND(find_text,within_text,[start_num])
    • find_text is the text that you’re looking for.
    • within_text is the string that you’re searching in.
    • if start_num is omitted, the search starts with the first character

FIND Traps

  • The FIND function will return the position of the first matching string, and it is case sensitive. For a search that is not case sensitive, use the SEARCH function, which we saw later earlier the 30XL30D challenge.
  • You can’t use wildcard characters in the find_text string. For wildcards, use the SEARCH function.

Example 1: Find Text in a Text String

To find specific text in a text string, you can use the FIND function. It is case sensitive, so in the screen shot below, the first two “i” characters are ignored, because they are lower case.
=FIND(B5,B2)
To handle errors, if the text is not found, you can wrap the FIND function with IFERROR (in Excel 2003 or earlier, use IF and ISERROR).
=IFERROR(FIND(B5,B2),”Not Found”)
Find01

Example 2: Find exact values from a list

Because FIND is case sensitive, you can use it to find exact strings in another string. In this example, there are valid codes listed in column E. With the FIND function, we can identify the ID numbers in column B that contain one of the valid code strings.
This formula must be array-entered, by pressing Ctrl + Shift + Enter
=IF(OR(ISNUMBER(FIND($E$2:$E$4,B2))),”Yes”,”No”)
Find02

Example 3: Find street name in address

In the next example, most of the addresses in column B start with a street number. With the formula in column C, we check for a number in the first character. If it’s a number, the FIND function locates the first space character, and the MID function returns all the text from the next character, to the end.
=IF(ISNUMBER(–LEFT(B2,1)),MID(B2,FIND(” “,B2)+1,LEN(B2)),B2)
Find03

Download the FIND Function File

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

Watch the FIND Video

To see a demonstration of the examples in the FIND function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Locate Text With Excel FIND Function

_____________