Wildcards with VLookup, HLookup and Lookup functions

Wildcard functionality in vlookup without entering wildcards

VLookup looks for an exact match of the lookup value, however wildcard functionality can be used in the lookup value. If you would like users to be able to enter a part of a value to find a match without the need for wildcards, follow the instructions below. These instructions are also valid for the HLookup and Lookup functions. Take note that the VLookup function in its default form returns only the first match found, so the order of items in the search area (table array) is critical.

Looking for Excel training in Sydney? Click here for our courses.


Type the VLookup function as you normally would and type “*”& lookup value &”*” around the lookup value.

For example, if the lookup value is located in cell B1, type =VLOOKUP(“*”&B1&”*”,etc into the cell.

The example to the left shows this functionality in use, with QRD matching text in the value A85-QRD-700 and successfully returning Edward’s design into the result cell, despite only part of the value being entered into the input cell with no wildcards.

 


VLookup (as well as HLookup and Lookup) in its standard form has wildcard functionality and allows wildcards to be used in the lookup value, as shown to the left.

As usual, * indicates any number of wildcard characters and ? indicates a single wildcard character.

The example to the left shows this functionality in use. Two letters (A and K) and two dashes (-) are in specific locations with ? indicating single wildcard characters in their place, followed by * indicating any number of wildcard characters in its place, which successfully finds A60-KDW-550 and returns David’s design.