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?

Continue reading “Excel VLOOKUP Problem Numbers”

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!

Continue reading “Keeping Track of Garbage in Excel”

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?

Continue reading “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!

Continue reading “Enter Complex Excel Formulas Fast”

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.

Continue reading “Choose From a List to Change Excel Data”

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.

Continue reading “Highlight the Latest Lottery Number Matches”

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!

Continue reading “Excel VLOOKUP Sorting Problem”