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 that number, a VLOOKUP formula should find the category in a lookup table
- Problem – Instead of a category name, the result is an #N/A error.
The VLOOKUP formula looks okay, so why doesn’t it work?
Continue reading “Excel VLOOKUP Problem Numbers”
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 how things are going, I’d like to analyze that food tracker data in Excel, instead of being limited to the preset options in LoseIt.
Continue reading “Analyze Food Tracker Data in Excel”
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 causing the problem? Take the challenge yourself – download the workbook to see how you’d solve it.
Continue reading “Take the Excel COUNTIF Challenge”
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 use Excel to schedule everything that has to be done. We’ve been using this meal planner for years now, and would be lost without it!
Continue reading “Create a Schedule 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 did a different type of garbage tracking. Maybe I went a little overboard, but you’ve probably done that too, right? Anyway, here’s how I spent way too much time on an Excel project, but had fun doing it. Don’t judge!
Continue reading “Keeping Track of Garbage in Excel”
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 two pieces of information, such as a product name, and a size, and you want to find the price based on that information? How can you do an Excel lookup with two criteria?
Continue reading “Excel Lookup With Two Criteria”
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 to enter complex Excel formulas fast.
I’ve been using Excel for a long time, and I’ve never seen this suggestion before. And it’s a real time saver!
Continue reading “Enter Complex Excel Formulas Fast”
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 rate for the selected region. I added an English/French selector to a workbook last week, so a formula would work correctly, in either language.
Continue reading “Choose From a List to Change Excel Data”
Did you win the big prize? Have you ever picked those winning numbers? If you’re keeping track of lottery ticket numbers, use Excel conditional formatting to highlight the winning numbers in a list. Each week, enter the numbers from the latest draw, and Excel will colour all the cells that have matching numbers. You can use a different colour to highlight the latest lottery number matches. The screen shot below shows an example.
Continue reading “Highlight the Latest Lottery Number Matches”
You can use an Excel formula to pull data from a lookup table – for example, enter a product name, and automatically see its price. Be careful though, or things can go horribly wrong, and you’ll end up selling things at the wrong price.
In this example, I used the VLOOKUP function to show what can go wrong. The same thing can happen with other functions too, such as an INDEX/MATCH formula. In fact, its more likely to happen there!
Continue reading “Excel VLOOKUP Sorting Problem”