Are you struggling to find an exact match for a specific string in Google Sheets? Look no further! In this tutorial, I’ll show you how to use the Vlookup Identical String Match Array Formula to achieve just that.
Understanding Vlookup Identical String Match
Before we dive into the formula, let’s clarify what we mean by “Vlookup Identical String Match.” Typically, when performing a Vlookup search, the search key will match similar strings regardless of case sensitivity. For example, the search key “Train” will match with “train,” “TRAIN,” and “Train.” However, in some cases, we want to match only the string that is identical in characters and cases, such as “Train” itself.
To achieve this, we can use the EXACT function in Google Sheets. We’ve covered the non-array solution in our previous tutorial titled “Case Sensitive Vlookup in Google Sheets [Solved].” However, in this post, we’ll focus on the array version, which supports multiple search keys in a single formula.
Sample Data and Criteria
Let’s start by looking at our sample data and criteria for the Vlookup Identical String Match. In the table below, we have a lookup range (A4:A) representing the products and an output range (F4:F) showing the unit rates.
The search criteria or keys are listed in I3:I4. Our goal is to search for these criteria in the table (A4:G) and return their corresponding unit rates.
The Vlookup Identical String Match Array Formula
Now, let’s delve into the Vlookup Identical String Match Array Formula itself. In cell J3, I’ve used the following formula:
=ArrayFormula(vlookup(I3:I4&TRUE,{A4:A®exmatch(A4:A,"^"&textjoin("$|^",true,I3:I4)&"$"),B4:G},6,0))
This formula performs an exact match for multiple search criteria in Google Sheets. But how does it work? Let me explain.
Formula Explanation
To understand the formula, let’s break it down into its individual parts.
Step 1:
The first part of the formula is:
regexmatch(A4:A,"^"&textjoin("$|^",true,I3:I4)&"$")
Copy this part into cell H4, and it will match the search keys in I3:I4 with the values in A4:A, returning TRUE for matches and FALSE for mismatches. This method ensures a case-sensitive and exact match, also known as an identical string match.
Step 2:
Now, let’s look at the rest of the formula using the standard Vlookup syntax:
Array_Formula(VLOOKUP(search_key, range, index, [is_sorted]))
To adapt the formula for our multiple criteria exact match, we need to modify the parameters as follows:
search_keybecomesI3:I4&TRUEbecause we will concatenate the output of Step 1 with the search column (the first column in the range).rangebecomes{A4:A&H4:H,B4:G}. Replace H4:H with the corresponding formula (Step 1 output).indexremains as 6 (unit rate column).is_sortedis set to 0.
And that’s it! Thanks for staying tuned. Enjoy using the Vlookup Identical String Match Array Formula in Google Sheets!
