The **VLOOKUP** function is a very dynamic tool in Google Sheets. It’s one of the most used functions in Google Sheets. We can use this function to look for any value vertically in any given range of data. In this article, I’ll show you 8 suitable examples of how to use the **VLOOKUP** function in Google Sheets with clear images and steps.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download Google Sheets from here and practice very quickly.

**What Is VLOOKUP Function in Google Sheets?**

The **VLOOKUP** function in Google Sheets searches for any information by row.

**Syntax**

The syntax of the **VLOOKUP** function is like this below-

`=VLOOKUP(search_key, range, index, [is_sorted])`

**Arguments**

Argument | Requirement | Function |
---|---|---|

Search key | Required | Reference or value to search for |

Range | Required | Reference where to search for |

Index | Required | The column no of the return value |

Is sorted | Optional | Data is sorted or not (FALSE for the exact match and TRUE for the approximate match) |

**Output**

The formula **VLOOKUP****(10003,A2:B6,2,FALSE) **will search for the value **10003** in the range **Cell** **A2** to **B6** and returns an exact value from **Column No 2**.

**8 Suitable Examples to Use the VLOOKUP Function in Google Sheets**

Let’s get introduced to our dataset first. Here we have some products in **Column B**, their quantities in **Column C,** and their prices in **Column D**. We’ll now see 8 useful examples of how to use the **VLOOKUP** function in Google Sheets with the help of this dataset.

**Example 1: Using VLOOKUP Function for Different Search Values**

Firstly, we’ll use the **VLOOKUP** function for searching different values. We have 2 values in our dataset as you can see below. We’ll search for them in our dataset.

**Steps:**

- At first, type the following formula in
**Cell C15**–

`=VLOOKUP(B15,$B$5:$D$12,3,FALSE)`

- Then, hit
**Enter**to get the corresponding price.

- Finally, you’ll see that the results are matching with the dataset.

**Example 2: Applying VLOOKUP Function for Different Column Index**

Now we have put different column index numbers beside our search values and so it will return values from different columns. You’ll find this in the attached picture. And we want to search for the values based on them. Let’s see the steps of how to use the **VLOOKUP** function in Google Sheets.

**Steps:**

- First of all, write the following formula in
**Cell D15**–

`=VLOOKUP(B15,$B$5:$D$12,C15,FALSE)`

- After that, press
**Enter**to get the output.

- Thereafter, use the
**Fill****Handle**tool for the rest of the cells.

- At last, you’ll find that the results are different because we have put different column index numbers.

**Example 3: Utilizing VLOOKUP Function for Exact Match**

Now we have a slightly different dataset. We have product ids in **Column B**, products in **Column C,** and their prices in **Column D**. We want to exactly match the product id in the below picture with our dataset and want to get the corresponding product. So we’ll insert **FALSE **into the **VLOOKUP** function to exactly match the value.

**Steps:**

- In the first instance, put the following formula in
**Cell C15**–

`=VLOOKUP(B15,$B$5:$D$12,2,FALSE)`

- Thereafter, click
**Enter**to get the product.

- Ultimately, the exact result will be the output.

**Example 4: Employing VLOOKUP Function for Approximate Match**

We have to match a product id, **102** which is not present in our dataset. Therefore we’ll insert **TRUE** into the **VLOOKUP** function to approximately match the value.

**Steps:**

- In the first place, insert the following formula in
**Cell C15**–

`=VLOOKUP(B15,$B$5:$D$12,2,TRUE)`

- Afterward, hit the
**Enter Button**to get the product.

- In the end, notice that our product id is
**102**. But it is giving the result for product id**101**. So we are getting an approximate result.

**Example 5: Combining IFNA and VLOOKUP Functions to Find Error**

If we search for a product that is absent in our dataset, then using the **VLOOKUP** function will give an error message. But we want to get our output as **“Not Found”**. We can do this by combining the **IFNA** and **VLOOKUP** functions. So we’ll search for the product **Fax Machine** which is not present in the dataset and bring out the result as **“Not Found”**.

**Steps:**

- In the beginning, type the following formula in
**Cell C15**–

`=IFNA(VLOOKUP(B15,$B$5:$D$12,3,FALSE),"Not Found")`

- Consequently, press the
**Enter Button**to get the result.

**Formula Breakdown**

**VLOOKUP(B15,$B$5:$D$12,3,FALSE)**

First, this function will look for the value in **Cell B15** from the range **Cell B5** to **D12** and give the price.

**IFNA(VLOOKUP(B15,$B$5:$D$12,3,FALSE),”Not Found”)**

Then this function will give **“Not Found”** as output because the **VLOOKUP** function is not finding the value in the dataset.

**Example 6: Using VLOOKUP Function for Multiple Criteria**

Look at a different problem now. We have both the product id and product. And we want to get results for these multiple criteria using the **VLOOKUP** function. For this purpose, we need to create a helper column first. Then we have to unite the **VLOOKUP** and **JOIN** functions together. Let’s see how to do it.

**Steps:**

- Before all, write the following formula in
**Cell B5**–

`=JOIN(" ",C5:D5)`

- Again, click the
**Enter Button**to get the output.

- Moreover, apply the
**Fill****Handle**tool to get results for**Column B**.

- Then, you’ll get that the product ids and products are in a combo in
**Column B**.

- Next, insert the following formula in
**Cell D15**–

`=VLOOKUP(JOIN(" ",B15:C15),$B$5:$E$12,4,FALSE)`

- After that, hit
**Enter**to get the price.

**Formula Breakdown**

**JOIN(” “,B15:C15)**

Foremost, this function will join the values of **Cell B15** and **C15**.

**VLOOKUP(JOIN(” “,B15:C15),$B$5:$E$12,4,FALSE)**

Next, this function will look for the joined values in our dataset and gives the result.

- Finally, we are getting results for both criteria, product id, and product.

**Example 7: Assigning VLOOKUP Function with Wildcard**

We can further use the **VLOOKUP** function with **Wildcard** to get results for partial matching. We’ll use the **Asterisk Wildcard (*)** with the function to get results for the character **“Key” **in our case. These characters will match with the product **Keyboard** and give its price.

**Steps:**

- Earlier on, put the following formula in
**Cell C15**–

`=VLOOKUP("Key*",B5:D12,3,FALSE)`

- Then, press
**Enter**to get the result.

- At last, you’ll see that it is matching with the dataset and giving the actual result.

**Example 8: Applying VLOOKUP Function on Another Sheet**

Consequently, we can apply the **VLOOKUP** function in another Google Sheet. I’ll show 2 processes regarding this.

**8.1: Another Sheet in Same File**

We can use the **VLOOKUP** function in another sheet of the same Google Sheet.

**Steps:**

- Before, rename the original sheet as
**Dataset**.

- Next, move to a new sheet and write the following formula in
**Cell C5**–

`=VLOOKUP(B5,Dataset!B5:D12,3,FALSE)`

- After that, click
**Enter**to get the result instantly.

**8.2: Another Sheet in Different File**

Additionally, we can also use the **VLOOKUP** function in another sheet of a different Google Sheet. For this purpose, we’ll combine the **VLOOKUP** function with **the ****IMPORTRANGE function**.

**Steps:**

- Initially, give a name to our original Google Sheet. Here we have given the name
**“how to use the vlookup function in google sheets”.** - Moreover, give a name of the sheet also. Here we have given the name
**“Dataset”.**

- Then go to an entirely new Google Sheets and give it a different name. Like we have given it the name
**“how to use the vlookup function in google sheets 2”.** - We’ll now look for the product
**Keyboard**from our original Google Sheets.

- So copy the link from the original Google Sheet.

- Then, insert the following formula in
**Cell C5**–

`=VLOOKUP(B5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VOMwxjptrgLwV3y0DjoixNgnFvtK95c-dUXF67H64js/edit#gid=0","Dataset!B5:D12"),3)`

- Afterward, hit the
**Enter Button**to get the result quickly.

**Formula Breakdown**

**IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1VOMwxjptrgLwV3y0DjoixNgnFvtK95c-dUXF67H64js/edit#gid=0″,”Dataset!B5:D12”)**

Foremost, this function will import data from the previous Google Sheets.

**VLOOKUP(B5,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1VOMwxjptrgLwV3y0DjoixNgnFvtK95c-dUXF67H64js/edit#gid=0″,”Dataset!B5:D12”),3)**

Next, this function will look for the values of **Cell B5** in the imported data and give results.

- In the end, you can notice that the
**VLOOKUP**function is working fine in the new Google Sheets.

**How to Unite INDEX, MATCH and EXACT Functions for Case Sensitive Values Instead of VLOOKUP Function in Google Sheets**

The **VLOOKUP** function is case insensitive. So if we have such data which has case-sensitive values then using the **VLOOKUP** function will give erroneous results. To solve this issue we can use the combination of the **INDEX****, ****MATCH**, and **EXACT** functions. Now we have a case-sensitive value which is **“desktop”** and we’ll look for it in our dataset by using these functions.

**Steps:**

- Firstly, type the following formula in
**Cell C15**–

`=INDEX(D5:D12,MATCH(TRUE,EXACT(B5:B12,B15),0))`

- Consequently, press the
**Enter Button**to get the price.

**Formula Breakdown**

**EXACT(B5:B12,B15)**

At first, this function tests for the identical value in **Cell B15** in the range **Cell B5** to **B12**.

**MATCH(TRUE,EXACT(B5:B12,B15),0)**

Next, this function gives the relative position of the identical value.

**INDEX(D5:D12,MATCH(TRUE,EXACT(B5:B12,B15),0))**

Lastly, this function gives the result based on matching values.

- Finally, you’ll see that the small case letters are matching with the small case and giving correct results.

**How to Merge INDEX and MATCH Functions Instead of VLOOKUP Function to Search on Left Side ****in Google Sheets**

The **VLOOKUP** function has a limitation. It can only look for a value on the right side and the lookup value must be in the first column of the range. But what if we want to search on the left side of the data? There is a very easy solution to that. We can merge the **INDEX **and **MATCH **functions to search on the left side. Like now we want the names of the products based on price. Let’s see the steps.

**Steps:**

- First, write the following formula in
**Cell C15**–

`=INDEX(B5:B12,MATCH(B15,D5:D12,0))`

- Again, click the
**Enter Button**to get the product.

**Formula Breakdown**

**MATCH(B15,D5:D12,0)**

Before, this function returns the relative position of the value in **Cell B15** from the range **Cell D5** to **D12**.

**INDEX(B5:B12,MATCH(B15,D5:D12,0))**

Then this function gives the output from **Cell B5** to **B12** based on the former match.

- Ultimately, you’ll get the exact match.

**Conclusion**

That’s all for now. Thank you for reading this article. In this article, I have discussed 8 suitable examples of how to use the **VLOOKUP** function in Google Sheets. I have also discussed what to do if there is case-sensitive value. In addition to that, I have talked about the process if you want to search for any value on the left side of the dataset. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our **officewheel.com****.** Visit the site and explore more.