How to Use VLOOKUP in Merged Cells in Google Sheets

This guide will explain how to use VLOOKUP in merged cells in Google Sheets to obtain the correct results. 

In other words, we can get the correct results if we learn how to use VLOOKUP in merged cells. Otherwise, we usually get incorrect results when the data we are looking for is in merged cells.

The rules for using the VLOOKUP function are as follows:

• The index must not be less than 1 or greater than the number of columns in the specified range.
• The specified range must be arranged in ascending order when is_sorted is TRUE.
• The specified range is not sorted when is_sorted is FALSE.

The VLOOKUP function is useful when searching for specific data from another column or range. So VLOOKUP will search the column we specify that contains the data we are looking for and pull the matching data into another column or range.

This function is often used when working with interrelated data. It is useful when we want to find information across different ranges or sheets. 

Although VLOOKUP is a straightforward function and is easy to use, it becomes difficult when the data we are searching for has merged cells. Because when we merged the cells, the value is only inputted in the first cell of that range. 

Let’s take an example.

Suppose you are looking for the best flights from different airlines for your vacation. Since there are multiple entries from each airline, you merged the cells. Once you create a table to organize the information, just use VLOOKUP to search and pull the data. 

But, you get incorrect results because of the merged cells. So you filled down the merged cells to make sure every cell has value. After, you use the VLOOKUP to get the correct results. The VLOOKUP function is not used as straightforwardly when dealing with merged cells. 

Great! Let’s move on and discuss how we write the VLOOKUP function in Google Sheets.

The Anatomy of the VLOOKUP Function

The syntax or the way we write the VLOOKUP function is as follows:

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

Let’s dissect this formula and understand what each term means:

• = this is how we start any function in Google Sheets.
• VLOOKUP() is our VLOOKUP function. This function will search or look up a specific column or range that contains the data you are looking for. Then, it will return the value that exactly or approximately matches.
• search_key refers to the data or value you are looking for. This can be any number, text, or cell reference.
• range is the columns where you will search or look for the search_key. Also, this is where you pull the value or data from. Remember, VLOOKUP in Google Sheets cannot look at its left and always look from the first column of the range.  
• index refers to the column in the range containing the data or value you want to pull or return. An error is returned if the index is less than 1. Similarly, an error is returned if the index is greater than the columns in the specified range. 
• is_sorted is an optional argument in the formula. When you input TRUE or omit this part, it means the column you are looking in, or the lookup column is sorted in ascending order. Otherwise, it will return FALSE if it’s  not sorted. 

A Real Example of Using VLOOKUP in Merged Cells in Google Sheets

Look at an example of using VLOOKUP in merged cells in Google Sheets. First, we will have two tables. One is where we will input the return data after VLOOKUP. Let’s call this the main table.

The lookup table is where we will search and pull the data. This table can be on the same sheet or a different sheet.

For instance, we are organizing the best flight for a trip. Since we have already decided the time for each airline we want, we need to search for the price of each flight. 

The lookup table contains merged cells, making it challenging to use VLOOKUP to get the correct price. That is why we will first input a value in every cell in the merged cell before performing VLOOKUP. Since merged cells only input the value in the first cell, the rest do not have any value. 

Let’s see what it would look like after successfully using VLOOKUP in merged cells to pull the prices of each flight.

You can make your own copy of the spreadsheet above using the link attached below. 

Now that we have seen an example of how to use VLOOKUP in merged cells in Google Sheets. Finally, it’s time to learn how to apply this to our work. 

How to Use VLOOKUP in Merged Cells in Google Sheets

This section will explain how to use VLOOKUP in merged cells in Google Sheets.

1. First, start the function in the main table. In this case, we will input the formula in D3. Next, select your search_key or the data you are looking for. So we are looking for Airline and Time which is in B3&C3.

2. Second, we need to fix the issue of the merged cell in the range portion of the formula. So input this formula: ‘{LOOKUP(ROW($F$3:$F$14),IF(LEN($F$3:$F$14),ROW($F$3:$F$14)),$F$3:$F$14)’.

3. Now, continue selecting the next column of our range, ‘ &$G3:$G$14‘. Then, we will select ‘$H$3:$H$14‘, which contains the data we want to pull or return. Finally, input the index and press Enter to return the results.

So the entire formula will be: =ARRAYFORMULA(VLOOKUP(B3&C3,{LOOKUP(ROW($F$3:$F$14),IF(LEN($F$3:$F$14),ROW($F$3:$F$14)),$F$3:$F$14)&$G$3:$G$14,$H$3:$H$14},2,0)).

4. Then, an auto-fill menu will usually appear to apply the formula to the rest of the rows. Select the check mark to apply this. You can also simply copy down the formula.

5. If the auto-fill does not appear or you cannot copy down the formula, change the search key. You can just copy and paste the formula in D4 and change the search key to B4&C4.

6. And tada! You have successfully performed VLOOKUP in merged cells and got the correct results.

That’s it! You have learned all about the VLOOKUP function and how to use it when dealing with merged cells in Google Sheets. 

Are you interested in learning more about what Google Sheets can do? You can now use the VLOOKUP function in Google Sheets and the various other Google Sheets formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe