Contextures

Contextures News

Excel VLOOKUP Problem

February 7, 2017

Fix a VLOOKUP problem, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Filter for Dates

Excel has many ways to filter for dates in an Excel table or in a pivot table. At the top of a date column, click the drop down arrow, then click Date Filters.

The top 4 options are static filters, where you can set a specific date or date range -- Equals, Before, After Between. Select one of those options, and enter your date(s).

Be careful with the Between option though, to make sure that it's doing what you expect.

  • The default settings are "is after or equal to" and "is before or equal to". That will include the start and end dates that you enter.
  • If you don't want to include the start and end dates, change those options to "is after" and "is before"

See more filter examples on the AutoFilters page on my Contextures website.

date filter Between

VLOOKUP Problem

Last week, someone asked me for help with a VLOOKUP problem:

  • His worksheet has a column with 8-digit numbers.
  • In another column, he uses the LEFT function to get the first 3 numbers
  • VLOOKUP formula should find the category, based on those 3 numbers, but shows #N/A instead.

The formula looks okay, so why doesn't it work? You can download the sample file, and see how you would solve it. There is a solution in the sample file, and let me know if you find a different way to fix it.

I'll post my solution, and your feedback, next week. You can read more about VLOOKUP on my website.

VLOOKUP problem

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Excel Budget - If you're planning a road trip this year, download Chandoo's Road Trip Planner Template, or get Hui's update (same page, further down). Which version do you prefer? (Level - All)

Excel Charts - If you're not sure whether to use a Column chart or Bar chart, Ann K. Emery shows examples of when to go vertical or horizontal. (Level - All)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

Do You Remember This?

If you've used Microsoft Office for a long time, you might remember the ancient times, when we had to install from floppy disks. This upgrade to Office 4.2, that I found while decluttering my office, had 25 disks! There were stacks of printed manuals too -- that big box was full of them. Ah, the good old days.

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: March 15, 2017 11:57 AM