## 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 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?

## 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 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.

## 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 causing the problem? Take the challenge yourself – download the workbook to see how you’d solve it.

## 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 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!

## 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 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!

## 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 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?

## 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 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!

## 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 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.

## Highlight the Latest Lottery Number Matches

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.

## Excel VLOOKUP Sorting Problem

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!