Author: Oscar Cronquist Article last 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

Related articles