HOW TO USE WILDCARD CHARACTERS IN VLOOKUP FUNCTION IN EXCEL

INTRODUCTION

VLOOKUP is one of the great functions of Excel. Every Excel learner wants to get perfection over this function but one can never be perfect without enough practice.
VLOOKUP provides a lot of flexibility in using this function.


FOR THE LEARNING VLOOKUP CLICK HERE.(FOR VLOOKUP IN HINDI CLICK HERE).
VLOOKUP EXAMPLES PART-I is here.


One of these flexibility is the ability to work with the Wildcard characters.
WILDCARD CHARACTERS ARE * ~ AND ?. THESE ARE USED TO LOOKUP THE VALUES ABOUT WHICH WE HAVE GOT PARTIAL INFORMATION.

 In this article we would learn about the way we make use of wildcard characters.

REVISION:
SYNTAX: VLOOKUP

The syntax ( the way how formula is phrased for excel) of VLOOKUP is

=VLOOKUP(cell address of value to be matched, range of cells to search, column number to return the value, match should be approximate or exact)


So, a sample format is here. Suppose the value to be found is in cell H13 and the table from which the value is to be extracted has the range I12:K25 (3 columns) and the match is to be exact.The format in the output cell will be

=vlookup(H13,I12:K25,3,false)

This will find out the value of H13 in the table I12:K25 and return the value of third column i.e. K if it could find H13 in the table’s first column.

WHAT ARE WILDCARD CHARACTERS IN EXCEL

WILDCARD CHARACTER help us in finding some words where we have any doubt.

For a quick reference

* is for portion of word e.g. sad* would go for all word starting with sad………

? would work for a single letter e.g. sad? would go for sad ….ending with a single letter.

In this case as we don’t know the name we’ll make use of wildcard character * and nest the two vlookups.

USING WILDCARD ENTRIES IN VLOOKUP

PROBLEM:

THERE IS A PERSON NAMED FLO…. BUT THERE IS SOME CONFUSION ABOUT THE REST OF THE NAME. FIND OUT THE DATE OF BIRTH OF THE PERSON.

The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).

Kindly paste it at H117. NAME SHOULD COME AT H117, So that the solution matches with yours.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAELmichael@abc.com12.02.19901200010
RAVIravi@abc.com15.03.1991100001
DANIELdaniel@abc.com14.05.2013300002
FLORENCEflorence@abc.com18.06.2000450005
TOMtom@abc.com25.07.2001980004
HARRYharry@abc.com31.7.1993650006
WILIAMwiliam@abc.com30.6.1991750009
EXCEL:VLOOKUP:EXAMPLE DATA SAMPLE

SOLUTION:

THERE IS A PERSON NAMED FLO…. BUT THERE IS SOME CONFUSION ABOUT THE REST OF THE NAME. FIND OUT THE DATE OF BIRTH OF THE PERSON.

PLANNING THE SOLUTION

This problem is not very apt for the data we are dealing with because its small for the better understanding. But when we are having large data these kind of small options can help us a lot.

In this problem, we will find out the name first and after that the date of birth.

For finding out we’ll use the wildcard character.

WILDCARD CHARACTER help us in finding some words where we have any doubt.

For a quick reference ONCE AGAIN

* is for portion of word e.g. sad* would go for all word starting with sad………

? would work for a single letter e.g. sad? would go for sad ….ending with a single letter.

In this case as we don’t know the name we’ll make use of wildcard character * and nest the two vlookups.

STEPS TO SOLUTION

  • Put the following formula in the cell M127
  • =VLOOKUP(VLOOKUP(“FLO*”,H118:H124,1,FALSE),H118:J124,3,FALSE)
  • The answer would appear as 18.6.2000 which is correct as the name is FLORENCE.

Let us now understand the formula.

  •  We have used two vlookups (Nested).
  • The outer vlookup starts and first argument is the name which is still to be found. For that we have applied another vlookup as VLOOKUP(“FLO*”,H118:H124,1,FALSE).
  • Inner Vlookup has the following arguments. The first argument “FLO*” would find the word matching FLO….. followed by any letters . H118:H124 is the column in which the names would be found. Column index number 1 will be returned as there is just one column and finally FALSE for exact match.
  • Inner Vlookup returns the answer as “FLORENCE”  which becomes the first argument of the outer VLOOKUP.
  • Second argument of the outer vlookup is H118:H124 which is the lookup table for the date of birth. Third column will be returned by the function and false for the exact match.
EXCEL:VLOOKUP:EXAMPLE :ANIMATED STEPS AND RESULT

THE ANSWER FOR THE PROBLEM IS 18.06.2000 WHICH IS CORRECT.

EXCEL:VLOOKUP:EXAMPLE :RESULT

USING WILDCARD ENTRIES IN VLOOKUP

PROBLEM:

THERE IS A PERSON NAMED R-VI WHERE – IS SOME MISPRINTED LETTER AND WE NEED TO FIND THIS NAME FROM THE GIVEN LIST.

The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).

Kindly paste it at G24. NAME SHOULD COME AT G24, So that the solution matches with yours.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAELmichael@abc.com12.02.19901200010
RAVIravi@abc.com15.03.1991100001
DANIELdaniel@abc.com14.05.2013300002
FLORENCEflorence@abc.com18.06.2000450005
TOMtom@abc.com25.07.2001980004
HARRYharry@abc.com31.7.1993650006
WILIAMwiliam@abc.com30.6.1991750009
EXCEL:VLOOKUP:EXAMPLE 6 DATA SAMPLE

SOLUTION:

THERE IS A PERSON NAMED R-VI WHERE – IS SOME MISPRINTED LETTER AND WE NEED TO FIND THIS NAME FROM THE GIVEN LIST.​

PLANNING THE SOLUTION

We are again taking a  small example for  better understanding. But when we are having large data these kind of small options can help us a lot.

In this problem, we want to find out the matching name in the given data where we don’t have the second letter.

For finding out we’ll use the wildcard character.

WILDCARD CHARACTER help us in finding some words where we have any doubt.

For a quick reference ONCE AGAIN

? would work for a single letter e.g. fat? would mean fat.. ending with a single letter for example e which would complete the word as fate. It can be at any place.

STEPS TO SOLUTION

  • Put the following formula in the cell H37
  • =VLOOKUP(“R?VI”,G25:G31,1,FALSE)
  • The answer would appear as RAVI which is correct as we can manually check.

Let us now understand the formula.

  •  We have used a single VLOOKUP as we are just demonstrating the use of ? wildcharacter.
  • The formula used is  =VLOOKUP(“R?VI”,G25:G31,1,FALSE).
  • The VLOOKUP has the search value which is the first argument as R?VI which means that ? can be filled with a single character which will be found in the VLOOKUP TABLE.
  • Second argument of the outer vlookup is G25:G31 which is the lookup table for the date of birth. FIRST [ AND ONLY IN THIS CASE ] column will be returned by the function and false for the exact match.

The animated process is shown below.

EXCEL:STEPS TO USE WILD CARD CHARACTER ?

THE ANSWER FOR THE PROBLEM IS RAVI WHICH IS CORRECT.

FINAL RESULT FOR WILD CARD CHARACTER USAGE