What Is VLOOKUP Partial Match In Google Sheets?
VLOOKUP Partial Match in Google sheets is a function used to find a value with partial match. This function is a lookup value and is part of VLOOKUP function.
The VLOOKUP partial match in Google sheets requires wildcard character such as asterisk (*). We need to use ‘FALSE’ value to find the exact match. VLOOKUP partial match is a default function in both Excel and Google sheets.
For example, consider the below table showing name and email ID of employees in columns A and B, respectively. The lookup value, ‘Billy’ is available in the cell B7.
Now, let us use the VLOOKUP Partial match formula to find the desired result.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, select the cell B8.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
So, the complete formula is =VLOOKUP(B7&”*”,A1:B5,2,FALSE)
Step 3: Press Enter key.
We can see the result in cell B8, as shown in the below image.
Likewise, we can find the VLOOKUP partial match function in Google sheets.
In this article, let us learn how to use VLOOKUP function in Google sheets with partial match.
Table of contents
Key Takeaways
- VLOOKUP partial match in Google sheets helps users find the value from the table range and is a type of VLOOKUP Google sheets function.
- Partial match works with partial value in the lookup table.
- There are three kinds of wildcards in Google sheets. They are: Asterisk (*), Question mark (?) and Tilde (~).
- Mostly, users use asterisk wildcard in Google sheets to partially find lookup value.
- Remember, the function returns #N/A! error when the lookup value is not available in the main table in Google sheets.
How To VLOOKUP Partial Match In Google Sheets?
We need to use the below steps to find VLOOKUP partial match in Google sheets.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. Next, select the cell where we want to find the result.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
Step 3: Press Enter key.
We can see the result in the active cell.
Likewise, we can find the VLOOKUP partial match function in Google sheets.
Examples
Let us learn how to use VLOOKUP Partial Match in Google sheets with examples.
Example #1
For example, consider the below table showing product and product ID in columns A and B, respectively. The lookup value, ‘Mango’ is available in the cell B7.
Now, let us use the VLOOKUP Partial match formula to find the desired result.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, select the cell B8.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
So, the complete formula is =VLOOKUP(B7&”*”,A1:B5,2,FALSE)
Step 3: Press Enter key.
We can see the result in cell B8, as shown in the below image.
Likewise, we can find the VLOOKUP partial match function in Google sheets.
Example #2 – Find Partial Match Between Two Columns In Google Sheets
For example, consider the below table showing employee names and ID in columns A and B, respectively. The lookup value, ‘Jack’ is available in the cell B7.
Now, let us use the VLOOKUP Partial match formula to find the desired result.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, select the cell B8.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
So, the complete formula is =VLOOKUP(B7&”*”,A1:B5,2,FALSE)
Step 3: Press Enter key.
We can see the result in cell B8, as shown in the below image.
Likewise, we can find the VLOOKUP partial match function.
Example #3 – VLOOKUP With Wildcard In Google Sheets
For example, consider the below table showing subjects and teachers in columns A and B, respectively. The lookup value, ‘Chemistry’ is available in the cell B7.
Now, let us use the VLOOKUP Partial match formula to find the desired result.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, select the cell B8.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
So, the complete formula is =VLOOKUP(B7&”*”,A1:B5,2,FALSE)
Step 3: Press Enter key.
We can see the result in cell B8, as shown in the below image.
Likewise, we can find the VLOOKUP partial match function in Google sheets.
Important Things To Note
- VLOOKUP partial match in Google sheets helps users identify values from a data.
- It is highly useful when users have to find for a particular value in 2 columns.
- Users need to include wildcard characters to find the desired result.
Frequently Asked Questions (FAQs)
For example, consider the below table showing items and price in columns A and B, respectively. The lookup value, ‘Laptop’ is available in the cell B7.
The steps are:
Step 1: To start with, insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, select the cell B8.
Step 2: Next, insert the VLOOKUP function in Google sheets and insert the wildcard character asterisk.
So, the complete formula is =VLOOKUP(B7&”*”,A1:B5,2,FALSE)
Step 3: Press Enter key.
We can see the result in cell B8, as shown in the below image.
Likewise, we can find the VLOOKUP partial match function in Google sheets.
• Ex* – This can match characters with ‘Ex’ in the beginning of the word. For example, Excellent, Excel are some words which we can match using this asterisk.
• *Ex – This can match characters with ‘Ex’ in the end. For example, Complex, FedEx are some words which we can match using this asterisk.
• *Ex* – This can match characters with ‘Ex’ anywhere in the word. For example, Text, Dextrose are some words which we can match using this asterisk.
The formula of VLOOKUP function in Google sheets is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
where
Lookup_value is the value which we want to search
Table_Array is the cell range used to look for a value
col_index_num is the column numbers to find the result
Range_lookup shows whether we want to include TRUE or FALSE exact match.
Download Template
This article must help understand the VLOOKUP Partial Match In Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What is VLOOKUP Partial Match In Google Sheets. We learn definition, how to use VLOOKUP Partial Match In Google Sheets. You can learn more from the following articles.
Clustered Bar Chart In Google Sheets
Leave a Reply