Menu

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.*

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.

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.

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.

NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |

MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |

RAVI | [email protected] | 15.03.1991 | 10000 | 1 |

DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |

FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |

TOM | [email protected] | 25.07.2001 | 98000 | 4 |

HARRY | [email protected] | 31.7.1993 | 65000 | 6 |

WILIAM | [email protected] | 30.6.1991 | 75000 | 9 |

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.

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.

- 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.

**THE ANSWER FOR THE PROBLEM IS 18.06.2000 WHICH IS CORRECT.**

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.

NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |

MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |

RAVI | [email protected] | 15.03.1991 | 10000 | 1 |

DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |

FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |

TOM | [email protected] | 25.07.2001 | 98000 | 4 |

HARRY | [email protected] | 31.7.1993 | 65000 | 6 |

WILIAM | [email protected] | 30.6.1991 | 75000 | 9 |

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

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.

- 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.

**THE ANSWER FOR THE PROBLEM IS RAVI WHICH IS CORRECT.**

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.