Excel's VLOOKUP function is excellent when you want to find a value in a table based on a lookup value. But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. This lesson shows you how to use the INDEX function (plus some other functions) to find all matching values in a list, and return a value from another column in the same row. It also looks at how to do this when you want to return all values which are a partial match (i.e. a wildcard search) to the values in your lookup table.
Welcome to Fiveminutelessons.com. Get free online courses where you can learn more about Google Analytics, Microsoft Excel, and Microsoft Word, and be more productive in just five minutes.
Search the site for help on a problem you have right now or browse the lessons below to improve your skills. We're adding new lessons all the time, so check back often.
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.
This lesson shows you how to calculate a running total between two dates in a column of data. An example might be calculating the total sales for the last 30 days up to today. Each row in the spreadsheet will calculate a new total based on the date in that row, counting back a specified number of days.
If you want to combine text with the results of a formula in a cell, you can use concatenation. Suppose you have calculated the total of a range of cells using a formula in cell D2. Now, you want to have cell A2 display the text "Today's sales are $12,000", where $12,000 is the value calculated in D2. As the value in D2 changes, you want the value in A2 to update automatically.
This lesson shows you how to group data in your pivot table by date. You can group by day, week, month, quarter or year. If your date fields include a time value, you can also group by seconds, minutes or hours. You'll also learn how to collapse and expand data groups in your pivot table so you can quickly see a summary of your data.
Mailchimp is a hugely popular email marketing platform, and for good reason. It's easy to use, and is free if your list is under 2000 subscribers. Yet a lot of people aren't using it effectively. Here are three things you might be doing wrong.
If you are using Google Adwords to drive traffic to your site, you absolutely should link your Google Adwords account with your Google Analytics account. If you don't, you are missing out on a lot of important analytics data, as well the opportunity to truly measure the ROI on your Google Adwords campaigns.
This lesson shows you how to use Conditional Formatting in Excel to format cells containing dates that are in the past, using a conditional formatting rule that compares the date in a cell with today's date, and formats it a different colour if it is in the past. We'll also extend this conditional formatting example to check the value of another cell as part of our criteria for applying the formatting.
The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.
Tables in Microsoft Word are great, but the default settings for tables are sometimes not what you want. In particular, Word will break rows with a lot of text across two pages if it needs to. If you'd rather have Word break tables up between pages so that each row is kept intact and not split across two pages, this lesson will show you how to do it. This lesson covers Microsoft Word 2007, 2010 and 2013 for Windows, and Microsoft Word 2011 for Mac.