# VLOOKUP Wildcard in Google Sheets

## What Is VLOOKUP Wildcard in Google Sheets?

If you have datasets on your spreadsheet, VLOOKUP helps you search for related information by row. When working with data, finding information across multiple sheets is very difficult. Thus, with VLOOKUP Wildcard in Google Sheets, we can look up and retrieve matching data from another table, either in the same sheet or a different sheet. For instance, we have some student roll numbers, names, and their status. We have to find if a student with a name beginning with “Ri” is present/absent. Enter the following formula in cell F2.

=VLOOKUP(\$F\$1&”*”,B2:C10,2,false)

The above formula looks for the first instance of the student’s name beginning with “Ri” and gives the corresponding output from the second column of the range, i.e., present.

###### Key Takeaways
• The VLOOKUP function in Google Sheets looks up and retrieves data from another table on the same sheet or a different sheet that matches the search value. It searches a column vertically for a supplied search key.
• The syntax for the Google Sheets VLOOKUP function is: VLOOKUP(search_key, range, index, [is_sorted])
• When you use VLOOKUP Wildcard in Google Sheets, you can use wildcards. Here,
• A question mark “?” matches any single character.
• An asterisk “*” matches any sequence of characters.
• To use wildcards in Google sheets’ VLOOKUP, you must use an exact match, i.e., “is_sorted = FALSE.”

As you must have seen in the example above, the VLOOKUP does a vertical lookup by searching for the specified value in the first column of the given range. It then returns a value from the same row of another column. Hence, the VLOOKUP function can be written as follows.

VLOOKUP(search_key, given range, index, [is_sorted])

• Search_key – this is the value we search for
• Range – the data range for the search. The first column of the range is searched for the search_key in this case.
• Index – the column number in the range from which a matching value should be returned(first column’s index is 1, 2nd column is 2 and so on).
• Is_sorted – indicates whether the lookup column is sorted (TRUE) or not (FALSE). The default is TRUE.

Notes:

1. If the fourth value is specified as TRUE, the first column must be in ascending order.
2. If is_sorted is FALSE, the first column does not have to be sorted.

### How to VLOOKUP Wildcard (*,?) in Google Sheets?

#### #1 – Typical VLOOKUP Function

VLOOKUP in Google Sheets helps you look up related information by row while looking up a value in a vertical column. However, in most cases, the lookup table might be on different sheets. Let us look at an example in which we retrieve the value from a different sheet. When you access a range in a table from another sheet, you write the range as follows: Sheet2!A3:B10.

Let us look at an example to understand this better.

The images show tables from two sheets. The first sheet contains details of some items and the number sold. The second sheet contains the cost of each item.

Sheet 4

Sheet 3

Step 1: Let us apply the VLOOKUP function. Here, we must search for each item on the first sheet with the corresponding unit cost on the second sheet. Enter the following formula in cell C2. First, let us look at the search value. In this case, it is “Pencils.”

=VLOOKUP(A2,

Step 2: Next is the crucial step where we must specify the range. Since the range is in Sheet3 while we are in sheet 2, we enter the range as follows.

=VLOOKUP(A2, Sheet3!A1:B5,

Step 3:  The last two arguments are the index of the column to be looked up and the value TRUE/FALSE, depending on whether the data is sorted. Since we are finding the cost of the number of items sold, the final formula is as follows.

=B2*VLOOKUP(A2,Sheet3!A1:B5,2,FALSE)

Step 4: Press Enter. You get the cost price of 69 pieces of the item sold(at \$1 per piece).

Step 5: Drag the Autofill handle to get the cost of other items sold. Before that, we fixed the reference of the range, so it does not change as Sheet3!\$A\$1:\$B\$5.

#### Example #1

Let us now look at the VLOOKUP Wildcard in Google sheets (similar to excel VLOOKUP wildcard). There is a table in the given sheet. It contains the details of the employee, like name and salary. Calculate the bonus amount received by employees whose name begins with a particular letter.

Let us set out to find the bonus of an employee whose name begins with the letter A if present in the data set. He is to be given a bonus of 10%, entered in cell E1.

Step 1: Let us suppose you want to find the bonus amount against the salary of an employee beginning with the letter A.

1. The first argument would be “A*”
2. The next argument would be the range where we would look up this value. Hence, it is A1:B8.
3. The third argument includes the column index, where we will retrieve the value from the same row.
4. The fourth argument is FALSE, as the data is not sorted.

Since we are calculating the bonus with the given percentage for the retrieved value, let us enter the following formula in E2.

=E1*VLOOKUP(“A*”,A1:B8,2,FALSE)

Step 2: Press Enter, and you get the bonus amount of the person whose name starts with A.

Step 3: Let us change A* to C* and check the results.

You can observe how the bonus amount changes from “Andrew” to “Charles.”

#### Example #2

As seen in the above example, we have used the value to be found directly in the formula. We can use the formula to search for a particular salary. Let us alter the example. We add another column before the employee name and give it the employee number.

Step 1: Now, let us look at an employee whose employee number begins with A. Three numbers are beginning with A. Enter the following formula in cell F2.

Step 2: We get the bonus amount of the first occurrence of the employee whose employee number begins with A3, which is John in this case.

#### #3 – Question Mark (?)

As seen earlier, the * is used to match a sequence of characters while Question mark (?) is used to match any single character, while looking up values. In other words, a question mark matches any single character.

In the example below, we have some flowers and the birth month they represent. Let us look up these flowers using a ? (question mark) and the VLOOKUP function.

Step 1: To apply the VLOOKUP wildcard in Google Sheets,  we will try to find the flower representing February. Apply the following formula in cell C7.

=VLOOKUP(“?????ary”,A2:B7,2,FALSE).

Step 2: As observed, we have given the search value as ?????ary. There are two months that end in “ary.” How does VLOOKUP distinguish between the two?

It is based on the number of ‘?.’ While January has just four letters before “ary,” February has five letters, and hence, since there are 5 ?, it returns the flower corresponding to February.

### Important Things to Note

1. Remember that VLOOKUP is not case-sensitive. It does not distinguish between lower and uppercase letters.
2. VLOOKUP Wildcards in Google Sheets always search in the first column of the range.
3. If VLOOKUP wildcard Google Sheets return wrong values, set the fourth argument to FALSE to return exact matches.
4. VLOOKUP can search using wildcard characters like the asterisk (*) and question mark (?).
5. As the VLOOKUP function cannot look to its left, for such scenarios, you may use the VLOOKUP in conjunction with INDEX MATCH.
6. The MERGE Sheets add-on in Google Sheets allows you to search without effortlessly using the formula.
7. If you mistakenly specify a range that is bigger than the maximum number of columns in the range, you get a #REF error.

1. How to look up data in columns to the left using VLOOKUP Wildcard in Google sheets?

The VLOOKUP function in Google Sheets searches and retrieves the data only to the right of the search term. In order to look up data to the left of the search term, we use the INDEX MATCH function in conjunction with VLOOKUP.

2. What are some common errors with VLOOKUP Wildcard with Google Sheets?

These are some of the common errors that can occur when there are mistakes in the VLOOKUP Wildcard in Google Sheets.

• You get the #NAME error if the search value is text data and you miss giving the quotes.
• An #REF! error occurs when the range is specified with a bigger value than the maximum number of columns in the range.
• AN #VALUE! error occurs when the index is less than one.

3. How is the VLOOKUP in Google Sheets different than HLOOKUP?

We have two different functions in Google Sheets that help look up and search for values. One is the VLOOKUP, which stands for Vertical LOOKU. You can use it to look up tables vertically, while HLOOKUP, which stands for Horizontal LOOKUP, is used to look up values horizontally. Besides this, the functionality of both functions is the same.