#vlookup examples

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: 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 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…