Untitled

Tutorial: How to use vlookup to add quarters to your date information (U.K. format)

Often we need to view our data by quarter. For example there may be quarterly sales numbers that you would like to view but currently you only have the date of each sale. This is possible using a vlookup. Remember the structure is: =vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

Example Quarterly Data Vlookup Scenario

In the example (quarters-tutorial-us-dates), we would like…

Tutorial: How to use vlookup to add quarters to your date information (U.K. format)

Often we need to view our data by quarter. For example there may be quarterly sales numbers that you would like to view but currently you only have the date of each sale. This is possible using a vlookup. Remember the structure is: =vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact ) Example Quarterly Data Vlookup Scenario In the example, we would like to populate column C in the…

Tutorial: How to use vlookup to add financial / fiscal year (U.S. Date Format)

It’s simple enough to group our data by calendar year in a pivot or using the YEAR() function but often we need to group data by financial year. Financial year start dates vary by geography and by company. This tutorial will show you how to add financial year labels to your data based on your date criteria. Follow along in the Financial Year Tutorial (US Dates).

Example Financial Year Vlookup…

Tutorial: Vlookup and the Wildcard Characters (* and ?) for partial matches

There are two wildcard characters in Excel:

an asterisk (*) which matches one or more characters, and

a question mark (?) matches a single character.

To use the asterisk character, we’ll use student names and numbers as a worked example.

In the first case (Cell B4 below), we’ll show that a standard vlookup can be used and the asterisk (*) can be used in the SearchFor cell B4

In the second case,…

How to use a vlookup formula to do price benchmarking

You might have a new list of prices from a supplier of products which you’d like to compare to another price list (your existing prices for example). The only way of linking them may be their product numbers. Rather than using the Find function again and again, you could use a vlookup. Follow along in the Price Benchmarking Tutorial Workbook.

We have the two tables on one worksheet for this…

Tutorial: Doing a vlookup for a word in a string

You might have a number of words in a string but you’re only wanting to lookup on one of them – the first word.

An example: You have a load of product descriptions which all start with the brand of the item. You’d like to include information about the product’s origin based on the brand.

Here is the original list. We want column B to show the location:

                We have the following…

How do I do a vlookup in VBA?

There are two ways that you might want to use this. Either (1) putting a vlookup in a cell or (2) using a vlookup to return a value in another way such as a message box.

This post assumes a basic knowledge of excel vba and macros.

Here’s an example list of names and results in columns A and B respectively.

(1) We’re going to use VBA to put a vlookup formula into cell F2

Press Alt + F11 to get…

Why is my vlookup formula returning #N/A when I know the value is in the lookup table

There are a few different ways this can happen:

1. The lookup table might be formatted differently, for example there might be cells that look like numbers but are formatted as text

2. There might be spaces around the value in the lookup table

The solutions

1. A clever way to fix this that I read recently is to first convert the SearchFor value to a string and then to a number to see if…

How do I use vlookup to test simply whether or not a value exists in another list?

In the following example we want to test for a value in a list but we just want to check that it’s there, not return any corresponding value. This is a good way to find out how many values in one list appear in another.

  Using our standard construction of a vlookup formula:

=vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

The SearchFor value will be cell C1

WhereToSearchwill…