The Vlookup function will work in merged cells as usual in Google Sheets, but the output won’t be what you are seeking. What’s the solution to it?

For example, if an item has three prices based on its grade, we may enter such data as below.

Item: Mango (A1:A3 merged and entered it).

Grades: Fancy, 1 and 2 (in B1, B2, and B3, respectively).

Price: $2.00, $1.95, $1.85 (in C1, C2, and C3, respectively).

How do we get the price of the Mango, grade 2?

=vlookup("Mango",A1:C3,3,0)

The above VLOOKUP formula will return $2.00, the price of Mango, “Fancy” grade.

To get the correct result after lookup, we should know how to use Vlookup in merged cells.

Let’s learn that below with two examples.

Examples to Vlookup in Merged Cells in Google Sheets

We will start with the above mango, grade, and price example.

Single Value in Search Column – Basic Non-Array Formula

Here is the above said multi-dimensional set of data (range A1:C3).

In cell C6, we require a Vlookup formula that returns the price of Mango 2, i.e., $1.95.

How do we do that?

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

The ‘search_key’ will be A6&B6.

We may require to fill down (virtually) the merged cells in A1:A3 in the Vlookup ‘range.’ Then combine it with B1:B3, similar to the search key combination.

Vlookup Merged Cell Formula # 1:

=ArrayFormula(
     vlookup(
        A6&B6,
        {lookup(row(A1:A3),if(len(A1:A3),row(A1:A3)),A1:A3)&B1:B3,C1:C3},
        2,
        0
     )
)

Formula Explanation:

search_key: A6&B6

range: {lookup(row(A1:A3),if(len(A1:A3),row(A1:A3)),A1:A3)&B1:B3,C1:C3}

The below part of the range fills down the value, i.e., “Mango.”

lookup(row(A1:A3),if(len(A1:A3),row(A1:A3)),A1:A3)

For detail, please read How to Fill Merged Cells Down or to the Right in Google Sheets.

Then I have combined the grades with them.

lookup(row(A1:A3),if(len(A1:A3),row(A1:A3)),A1:A3)&B1:B3

I have used the {} brackets to create a virtual range such as {mango & grade, price}.

{lookup(row(A1:A3),if(len(A1:A3),row(A1:A3)),A1:A3)&B1:B3,C1:C3}

The ‘virtual’ range now contains two columns. The price is in the second column.

So I have used index # 2 to denote the price column in the Vlookup. Please once again scroll up to have a look at the syntax above.

The above is a basic example of Vlookup in merged cells in Google Sheets.

Below you can find one more example with multiple merged values in the search column and array as well as non-array formulas.

Vlookup Merged Cells Array Formula in Google Sheets

We have a table (lookup table) containing the unit weight of a few structural steel materials, and they are “ISMB” and “ISMC” in different sizes (depths).

How do we search structural steel materials from another table (a measurement sheet table) in the lookup table and get their corresponding unit weights?

Didn’t get it?

As per the above, the lookup table is in the range G8:I16 (after skipping headers), which contains merged cells in column G.

In cell D2, we want to search A2&B2, i.e., ISMB125, in G8:G16&H8:H16, and return the value from I8:I16.

Repeat the same in cell D3 (where only the search key will change and become A3&B3), D4, D5, and so on.

Here also, the Vlookup won’t be a straightforward one as the lookup table contains merged cells.

We can follow our earlier formula to Vlookup merged cells in Google Sheets.

Steps

We will start with the D2 formula. Later on, we can either convert it to an array formula to expand down or drag it down.

Vlookup Merged Cell Formula # 2:

=ArrayFormula(
     vlookup(
        A2&B2,
        {lookup(row($G$8:$G$16),if(len($G$8:$G$16),row($G$8:$G$16)),$G$8:$G$16)&$H$8:$H$16,$I$8:$I$16},
        2,
        0
     )
)

If you follow our Vlookup Merged Cell Formula # 1 above, you won’t find any issue in learning Formula # 2.

Here we have filled down the values in G8:G16. In formula one, it was A1:A3.

search_key: A2&B2

range: {lookup(row($G$8:$G$16),if(len($G$8:$G$16),row($G$8:$G$16)),$G$8:$G$16)&$H$8:$H$16,$I$8:$I$16}

In this, the following part (in bold above) represents G8:G16, where merged cells are normalized by filling values.

lookup(row($G$8:$G$16),if(len($G$8:$G$16),row($G$8:$G$16)),$G$8:$G$16)

You can drag down the above Vlookup merged cells formula (in cell D2) as far as your want.

So the search_key will become A3&B3, A4&B4, and so on.

Alternatively, you can convert it to an array formula.

Vlookup Merged Cell Formula # 3 (Array Formula):

=ArrayFormula(ifna(vlookup(A2:A&B2:B,{lookup(row(G8:G16),if(len(G8:G16),row(G8:G16)),G8:G16)&H8:H16,I8:I16},2,0)))

The changes are minimal – A2&B2 has become A2:A&B2:B. Additionally, we have used IFNA with the Vlookup.

That’s all about how to Vlookup merged cells in Google Sheets.

Thanks for the stay. Enjoy!

Related Resources

1. How to Use Sumproduct with Merged Cells In Google Sheets.
2. Sequence Numbering in Merged Cells In Google Sheets.
3. Merge Duplicate Rows in Google Sheets and Concatenate Values.
4. Merge Two Tables in Google Sheets – The Ultimate Guide.
5. How to Find the Cell Addresses of the Merged Cells in Google Sheets.
6. Merge and Unmerge Cells and Preserve Values in Google Sheets.
7. How To Use Countif or Countifs In Merged Cells In Google Sheets.
8. Sort Vertically Merged Cells in Google Sheets (Workaround).
9. Copy-Paste Merged Cells Without Blank Rows/Spaces in Google Sheets.
10. How to Merge Two Columns Into One Column in Google Sheets.
11. How to Use Sumif in Merged Cells in Google Sheets.