Vlookup Skips Hidden Rows in Google Sheets – Formula Example

Are you tired of Vlookup returning incorrect results because it includes hidden rows in Google Sheets? Don’t worry, we have got you covered. In this tutorial, we will show you how to make Vlookup skip hidden rows and only consider the visible ones.

A Simple Solution: The Helper Column

To Vlookup only visible rows in Google Sheets, you need to create a helper column that contains a Subtotal formula. Here’s how you can do it:

1. Assume you have a sample data in cells A2:C8.

2. In column D, use the below Subtotal formula in cell D3 and drag it down to D8:

   =subtotal(103,A3)

   This formula will return the value 1 in visible rows and 0 in hidden rows.

   

With the helper column in place, you are ready to Vlookup only the visible rows in Google Sheets.

Formula Example: Vlookup Skips Hidden Rows in Google Sheets

Let’s assume you want to search for the name “Dominic” in column A (the first column) and retrieve the corresponding value from column B (the second column). However, there is a hidden row with the name “Dominic” in cell A4. Using the regular Vlookup formula (=vlookup("Dominic",A3:C8,2,0)), you would get the incorrect value of 600, which is in the hidden row.

To correctly exclude hidden rows in the Vlookup, use the following formula:

=ArrayFormula(vlookup("Dominic",If(D3:D8=1,A3:C8),2,0))

But how does this formula work? The key lies in the helper column. Instead of using the range A3:C8 for the Vlookup, we use a logical IF statement that checks the values in the helper column. Here’s the formula breakdown:

If(D3:D8=1,A3:C8)

By doing this, the Vlookup only considers the rows where the helper column value is 1. This ensures that hidden rows are excluded from the search.

Pro Tip: Remember to wrap the formula in ArrayFormula to get the desired results.

With this handy formula, you can now confidently make Vlookup skip hidden rows in Google Sheets.

Want to learn more? Check out other resources to enhance your Google Sheets skills:

1. Google Sheets Query Hidden Row Handling with Virtual Helper Column
2. SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column]
3. How to Omit Hidden or Filtered Out Values in Sum [Google Doc Spreadsheet]
4. Count Unique Values in Visible Rows in Google Sheets
5. Find the Average of Visible Rows in Google Sheets
6. Subtotal Function With Conditions in Excel and Google Sheets

Now you have the power to make Vlookup work seamlessly in Google Sheets and save yourself from incorrect results caused by hidden rows.

Discover more Google Sheets tips and tricks at Crawlan.com.