How to Return Exact Match in Google Sheets (7 Suitable Ways)

While working on a dataset sometime we need the same value from other cells or another sheet, sometimes we need to know the exact place of a particular character. In that case, matching every character manually or checking the character’s position every time is time-consuming and tiring work. We can use different types of formulas and functions to make this work easier. In this article, We will learn how to return an exact match in Google Sheets using some practical examples.


A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.


7 Suitable Ways to Return Exact Match in Google Sheets

The Dataset below contains Student Name and Total Marks. Here, we will show 7 practical yet simple examples to return an exact match in Google Sheets.

google sheets exact match

Suppose we want to lookup for an exact match to the student’s name and return the corresponding total marks. Follow the methods below to do that.


1. Using  VLOOKUP Function

Now we will use the VLOOKUP function to return an exact match in Google Sheets using the following dataset. So, let’s start.

📌 Steps:

  • First, select cell F5 to execute the VLOOKUP function.

Using  VLOOKUP Function to return exact match in google sheets

  • Then enter =VLOOKUP( into the cell and click on cell E5 as the lookup value.

  • After that, enter the range B5:C11 to extract the match value from the particular range.

  • Now, manually write 2 to get the value from the second column of the range and at last terminate the function with 0 or FALSE to get the exact value.
=VLOOKUP(E5,B5:C11,2,0)

Using  VLOOKUP Function return exact match in google sheets


2. Applying XLOOKUP Function

Here, we will apply the XLOOKUP function to return the exact match in google sheets using the following dataset. So, follow the steps below.

📌 Steps:

  • First, select cell F5 to execute the formula.

Applying  XLOOKUP Function to return exact match in google sheets

  • Then, enter =XLOOKUP( using the lookup value in cell E5 and the lookup range as B5:B11 as below.

  • After that, select the result range C5:C11 and write Null manually so that if the value doesn’t match it can return as null.

  • Now, locate the position of the value to get the exact return from the formula as below.
=XLOOKUP(E5,B5:B11,C5:C11,"Null",2,-1)


3. Utilizing INDEX and MATCH Functions

Here, we will utilize INDEX and MATCH functions to return the exact match in Google Sheets using the same dataset as before.

📌 Steps:

  • First, select cell F5 to execute the formula and insert the INDEX function.

Utilizing INDEX MATCH Functions to return exact match in google sheets

  • Then, enter the lookup range B5:C11 to shortlist the area of the match.

  • After that, enter the MATCH function and select lookup value cell E5.

  • Now, select the result range B5:B11 and end the range with 0 to close the range within column B.

  • At last, complete the formula with 2 to get the exact return from the second column of the lookup range.
=INDEX(B5:C11,MATCH(E5,B5:B11,0),2)


4. Executing REGEXMATCH Function

Here, we will execute the REGEXMATCH function to return the exact match in google sheets using the same dataset as before.

📌 Steps:

Executing REGEXMATCH Function to return exact match in google sheets

  • Then enter the REGEXMATCH function and select result range B5:B11 to get the value from the exact range.

  • After that, use ^David$ to return TRUE for an exact match to David from the dataset and the rest values will appear as FALSE as below.
=ARRAYFORMULA(REGEXMATCH(B5:B11,"^David$"))


5. Using Basic MATCH Function

Now, we will execute the MATCH function to return the row number of the exact match from the other dataset in Google Sheets using the same dataset as before.

📌 Steps:

  • First, select cell F5 and enter the function using cell C5 as the lookup value.

Using Basic MATCH Function to return exact match in google sheets

  • Now, select the lookup range B5:B11 and complete the function with 0 to get the exact match. The formula will return the row number for the exact match.
=MATCH(E5,B5:B11,0)


6. Applying the HLOOKUP Function

Here, we will apply the HLOOKUP function to return the exact match in google sheets using another dataset below. This dataset contains Student Name and Total Number.

Applying HLOOKUP Function to return exact match in google sheets

📌 Steps:

  • First, select cell F5 to execute the HLOOKUP function.

  • Then enter =HLOOKUP( into the cell using the lookup value in cell G5.

  • After that, enter the range C4:E5 to extract the match value from the particular range.

  • Now, manually write 2 to get the value from the second column of the range, and at last complete the formula with 0 or FALSE to get the exact value.
=HLOOKUP(G5,C4:E5,2,0)


7. Using Find and Replace Tool

Here, we will use the Find and Replace tool to return the exact match in google sheets using the same dataset as before.

📌 Steps:

  • First, select the lookup range B5:C11 and press Ctrl + H. Then the Find and Replace window will pop up as below.

Using Find and Replace tool to return exact match in google sheets

  • Now Manually write David into the Find box as the lookup value is David.
  • After that, select This sheet in the Search criteria to search within the active sheet only. Next, select Match case, Match entire cell contents and Search using regular expressions to get the exact match as a return.

  • Then, click the Find option and the search value will appear as the final output.


Things to Remember

  • Use the ARRAYFORMULA function while executing the exact match with the REGEXMATCH function.
  • Basic MATCH function will return to the position of the exact match, Match function doesn’t return to the value.

Conclusion

In this article, we explained how to return an exact match in Google Sheets with simple and practical examples. Hopefully, the examples will help you to apply this method to your own dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo