Function Problems

How to Make Excel VLOOKUP Ignore Filtered-Out Rows

Algirdas Jasaitis Sep 28, 2026 871 views

Question details

The user wants to know why VLOOKUP includes hidden or filtered rows in its search results and how to perform a lookup that exclusively considers visible rows.

How to Make Excel VLOOKUP Ignore Filtered-Out Rows

Product: Excel
Device & OS: not provided
Scenario: Performing a data lookup operation on a dataset with active filters applied.
Observed behavior: VLOOKUP returns values from rows that are currently hidden by filters, instead of restricting its search to visible records.

Before you start

Before proceeding, ensure you have correctly applied your filters and clearly identified the data range you want to perform the lookup on. Save a backup of your workbook, as complex array formulas can occasionally slow down large datasets.

Solution 1Recommended

Use an Array Formula with SUBTOTAL and OFFSET

Combine VLOOKUP with SUBTOTAL and OFFSET to dynamically create a lookup array that only includes visible rows.

By default, VLOOKUP evaluates the entire specified range, regardless of whether rows are hidden by filters. To bypass this default behavior, you can nest the SUBTOTAL and OFFSET functions within an IF statement to generate a virtual array that excludes hidden rows.

Select the destination cell

Click on the cell where you want the lookup result to appear.

Input the array formula

Type the formula: =VLOOKUP(B37, IF(SUBTOTAL(3, OFFSET(range, ROW(range)-MIN(ROW(range)), 0, 1)), range), 1, FALSE).

Adjust the ranges

Replace 'B37' with your actual lookup value, and replace the word 'range' with your absolute data range (e.g., $A$2:$C$100). Adjust the column index number ('1') as needed.

Apply the array formula

Press Ctrl + Shift + Enter to apply the formula. In newer spreadsheet versions supporting dynamic arrays, pressing Enter is sufficient.

Use an Array Formula with SUBTOTAL and OFFSET

Understanding the SUBTOTAL function: The number '3' inside the SUBTOTAL function tells the spreadsheet to use COUNTA, which counts non-empty visible cells. This acts as a switch to distinguish between visible and filtered-out rows.

Solution 2

Use a Helper Column for Visible Rows

Add a helper column using the SUBTOTAL function to mark visible rows, which simplifies the lookup process.

Efficient Data Analysis

Easily Manage Complex Formulas with WPS Office

WPS Spreadsheet fully supports advanced array formulas, including combinations of VLOOKUP, SUBTOTAL, and OFFSET, allowing you to seamlessly analyze filtered data. It offers a lightweight, highly compatible alternative to handle large datasets effortlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook (.xlsx).
2. Apply Data Filters: Navigate to the Data tab and use the Filter tool to hide the rows you do not need.
3. Enter the formula: Type your combination of VLOOKUP, SUBTOTAL, and OFFSET exactly as you would in Microsoft Excel to extract only the visible data.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Supports advanced functions like VLOOKUP, XLOOKUP, and dynamic arrays.Free to download with a familiar, user-friendly interface.Lightweight application that runs smoothly even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP ignore filters by default?

VLOOKUP is designed to search the underlying data stored in the cell references, ignoring the visual display state. Therefore, it searches all data regardless of whether row heights are zero or rows are hidden by filters.

Can I use XLOOKUP to ignore filtered rows?

Like VLOOKUP, XLOOKUP also evaluates all rows within the specified range, including hidden ones. To search only visible rows with XLOOKUP, you must still combine it with other functions like FILTER or SUBTOTAL.

Why is my SUBTOTAL array formula returning an error?

Ensure you have entered the formula as an array formula by pressing Ctrl+Shift+Enter (in older software versions). Also, verify that the 'range' references in the OFFSET and ROW functions are identical and accurately match your data boundaries.

Excel & Data Problems