You can use the VLOOKUP function to find data in a lookup table, based on a specific value. If you enter a product number in an order form, you can use a VLOOKUP formula to find the matching product name or price. See how to use Excel VLOOKUP in different ranges.
NOTE: The examples below use VLOOKUP to get the value from the correct table. You could do a similar lookup with the INDEX and MATCH functions.
Lots of people visit my Contextures website looking for information on the Excel Count functions.
Counting seems like an easy thing to do in Excel, if you’ve been using the program for a while. But, if you’re just starting out, it might not be so obvious.
Count Quirks
Even if you’re an experienced Excel user, there are a few quirks with the Count functions, that you might not have noticed yet.
For example, different count functions treat “empty” cells differently, as you can see in the screenshot below.
In Row 5, the blue cells contain a formula that creates an empty string.
The COUNTA function counts that cell, even though it looks empty.
The COUNTBLANK function counts the cell too, even though it contains a formula.
There are many more tips, examples and videos on the Excel Count functions page on my Contextures site.
Video: 7 Ways to Count in Excel
To see a quick overview of 7 ways to get a total count of cells in Excel, watch this 77-second video. There are written steps for each count function on the Excel Count functions page.
Male or female? English or French? Yes, No or Maybe? Those are just a few of the choices that you can make with Option Buttons in Excel. When people select answers with Excel Option Buttons, you can provide a list of possible answers to a questions, and users can only select one answer from the list.
To do some research on sorting, I hauled one of the big, dusty Excel books off my shelf, to see if there were any scintillating sorting secrets to uncover. Under Sorting, I saw “rank calculations” so I turned to that page, to see what it said about calculating rank in Excel.
While working on a client’s sales plan last week, I had to count the orders for a couple of specific customers. This tutorial shows how to count cells with specific text in Excel.
If you’re having a party this weekend, you can plan your party seating with Excel. Get this sample Excel seating workbook, enter the guest names on the Lists sheet, then fill the tables by selecting names from data validation drop down lists. After you’ve assigned a guest to a table, that guest’s name disappears from the drop down lists, so you can’t accidentally assign a guest to two different seats.
You have two Excel lists, and you’re trying to find the items that are in both lists. You know there are matching items, but if your VLOOKUP formulas can’t find any matches, you might need to clean Excel data with TRIM and SUBSTITUTE.
You can create order forms and price lists in Excel, and automatically show a price when a product is selected in the order form. But what happens if you want to give some customers special pricing, or offer sales pricing occasionally? Here’s how to customize your Excel price list with VLOOKUP and MATCH.
Can you remember how old you are? Or are you like me, and have to ask, “What year is it?” and then subtract your birth year? Or do you like calculating ages in Excel — let it do the arithmetic for you!
That’s my dad in the picture below, proudly holding the catch of the day. He tried to teach me how to fish, but without much success. (Worms…ewwww.)
Fishing with OFFSET Function
This week someone asked me to explain the Excel OFFSET function, saying “Please teach me to fish.” That’s when it struck me that using OFFSET is similar to fishing.
When you’re fishing, you can dip into a pond with a bamboo pole and a small hook, or head out to sea, and cast a large net. Or you can fish the way we do in Canada, through a small hole in the ice, but that’s another story. (There’s a video at the end of this article.)
With the Excel OFFSET function, you can pull data from a single cell nearby, or a large range of cells off in the distance.
The OFFSET function is useful when you want to make the data selection adjustable.
For example, if a February date is entered in cell A2, you can sum the February expense column. If a March date is entered, sum the March expenses instead.
Your Fishing Equipment
To make the OFFSET function work, you’ll tell it 3 things:
The starting point
Where to go from there
How big a range to capture (optional)
The OFFSET syntax is: OFFSET(reference,rows,cols,height,width)
The reference is the starting point.
The rows and cols tell OFFSET where to go from the starting point. It can go up or down a specific number of rows, and left or right a specific number of columns.
The height and width set the size of the range. It can be as small as 1 row and 1 column (a single cell) or much bigger.
For example, this OFFSET formula would return the January total, in cell B6: =OFFSET(A1,5,1,1,1)
The starting reference is cell A1.
From there, it goes down 5 rows, and right one column, to cell B6.
The selected range size is 1 row tall and 1 column wide.
Baiting the Hook
Instead of typing all the values in the formula, you can use one or more cell references, to make the OFFSET formula flexible. In this example, all the totals are in row 5, so that number won’t change.
However, the month number is typed in cell G1, so you could use that cell to set the number of columns to offset. Change the formula so G1 is the cols argument.
=OFFSET(A1,5,G1,1,1)
Now, if you change the month number to 3 in cell G1, the March total will be returned.
Casting the Net
Instead of pulling the result from a single cell, you could use OFFSET with the SUM function, to select a range with multiple cells, and calculate the total.
For example, this formula would calculate the total for the February expenses.
=SUM(OFFSET(A1,1,G1,4,1))
The starting reference is cell A1.
From there, it goes down 1 row, and right 2 columns, to cell C2.
The selected range size is 4 rows tall and 1 column wide – C2:C5.
Other Fish to Fry
I like the OFFSET function, and use it to create dynamic ranges in some of my workbooks.
There are alternatives to using the Excel OFFSET function, such as the Excel INDEX Function.
There’s an interesting discussion of the merits of each function on Dick Kusleika’s Daily Dose of Excel Blog: New Year’s Resolution: No More Offset.
Ice Cold Fish
I’d rather stay inside and work on OFFSET formulas, but ice fishing is popular here in Canada. This video makes the sport look almost appealing.