PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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.
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.
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 |
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.
Let us now understand the formula.
THE ANSWER FOR THE PROBLEM IS 18.06.2000 WHICH IS CORRECT.
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 |
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.
Let us now understand the formula.
THE ANSWER FOR THE PROBLEM IS RAVI WHICH IS CORRECT.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE