Category: Excel Formulas

Combine VLOOKUP and MATCH for Flexible Formula

With the Excel VLOOKUP function, you can pull data from a specific column in a lookup table. For example, if the lookup table has Product ID, Product Name and Price, you’d use column 3...

Take the Excel Name Fix Challenge

Last week, in my Contextures Newsletter, I posted an Excel Name Fix challenge. There was a short list of names, which needed to be fixed. Then we needed to count how many were fixed....

Excel RANK IF Formula Example

Excel has a RANK function, so you can calculate where each number stands in a list of numbers. There isn’t a RANKIF function though, if you need to rank based on criteria. Someone asked...

Excel VLOOKUP Problem Numbers

Recently, someone asked me for help with VLOOKUP problem numbers: He had a list of 8-digit numbers. In the next column, there was a formula to get the first 3 numbers: =LEFT(A2,3) Based on...

Analyze Food Tracker Data in Excel

To keep track of what I’m eating, I use the LoseIt food tracker app on my iPhone and iPad. It’s easy to use, and creates nice summaries for each day and week. To see...

Take the Excel COUNTIF Challenge

Last week, in my Contextures Newsletter, I posted an Excel COUNTIF challenge. There were 100 codes on a worksheet, but when counted, the occurrences for each letter only added up to 99. What was...

Create a Schedule in Excel

This weekend is our Canadian Thanksgiving, and we’ll have 16 people here for dinner. To make sure that all the dishes are ready on time, I’ll create a schedule in Excel for everything that...

Keeping Track of Garbage in Excel

I’ve worked with “garbage” data many times in Excel. You know what I mean – data that is so messy it takes you hours (or days) to clean it up. This week though, I...

Excel Lookup With Two Criteria

If you need to get a product price in Excel, you can use VLOOKUP or INDEX/MATCH to get the price from a lookup table, based on a product code. But what if you have...

Enter Complex Excel Formulas Fast

On Chandoo’s blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week’s list, but my favourite one was down in the comments section – how...

Choose From a List to Change Excel Data

Do you need a quick way to show different data in a worksheet? For example, on an order form, you could let people select a region, and automatically include the shipping cost or tax...

Excel Sheet Selector With No Macros

If you’re setting up a workbook for other people to use, they’ll appreciate it if you make it easy to move around in the file. You can create a table of contents on the...