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.

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