Article updated on February 18, 2018

This post describes how to search visible values and return multiple values from a table. Some rows are hidden because of table filters. I am not using the vlookup function in this formula.

Example,

Array Formula in cell B14:

=INDEX($C$2:$C$9, SMALL(IF($B$11=(IF(SUBTOTAL(3, OFFSET($B$2:$B$9, MATCH(ROW($B$2:$B$9), ROW($B$2:$B$9))-1, 0, 1)), $B$2:$B$9)), MATCH(ROW($B$2:$B$9), ROW($B$2:$B$9)), ""), ROW(A1)))

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Filter table

Let´s remove all "A"´s from item column. Click black arrow near Item header. Deselect A. Click OK.

Remove #num errors

IFERROR(array_formula, "")  + Ctrl + SHIFT + ENTER.

Download excel 2007 file *.xlsx

vlookup a filtered table and return multiple values.xlsx

Functions in this blog post:

OFFSET(reference, rows, cols,  [height], [width])
Returns a reference to a range that is a given number of rows and columns from a given reference

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the row number of a reference

Recommended posts

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Populate a list box with visible unique values from an Excel table [VBA]

Excel tables, introduced in excel 2007, sort, filter and organize data any way you like. You can also format data […]

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.