## VLOOKUP in a filtered Excel Table and return multiple values

This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return multiple values. Some rows are hidden because of Excel Table filters, I am not using the VLOOKUP function in this formula.

Array Formula in cell B15:

#### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

#### Filter table

Let's remove all A's from item column. Press with left mouse button on black arrow near Item header. Deselect A. Press with left mouse button on OK.

### Explaining formula in cell B15

#### Step 1 - Create array

This step is necessary in order to be able to use the SUBTOTAL function in the next step. The ROW function returns row numbers based on a cell reference.

The MATCH function converts the row number array to an array that starts with 1.

OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1)

becomes

OFFSET(Table1[Number], MATCH({3;4;5;6;7;8;9;10}, {3;4;5;6;7;8;9;10})-1, 0, 1)

becomes

OFFSET(Table1[Number], {1;2;3;4;5;6;7;8} - 1, 0, 1)

becomes

OFFSET(Table1[Number], {0;1;2;3;4;5;6;7}, 0, 1)

becomes

OFFSET({2; 1; 2; 1; 2; 1; 2}, {0;1;2;3;4;5;6;7}, 0, 1)

and returns

{2; 1; 2; 1; 2; 1; 2}.

#### Step 2 - Which values are shown in the Filtered Excel table

The SUBTOTAL(103, array) counts the number of values that are not empty.

SUBTOTAL(103, OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1))

becomes

SUBTOTAL(103, {2; 1; 2; 1; 2; 1; 2})

and returns

{0; 1; 1; 0; 1; 1; 0; 1}

This array may look weird, the array in the SUBTOTAL function has 7 values and the result array has 8 values, how is it possible? This is, however, what you get if you convert the functions to constants by selecting them using and then pressing F9.

If you examine the formula using the "Evaluate Formula" tool you get the following array: {#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}

Even more confusing. This is what I think is going on. The OFFSET function actually returns an array of arrays, like this { {2}; {1}; {2}; {1}; {2}; {1}; {2} }

Then the SUBTOTAL function returns an array that indicates if the value is shown in the filtered table or not. 0 (zero) means not shown and 1 is visible.

{0; 1; 1; 0; 1; 1; 0; 1} meaning row 3, 6 and 9 are hidden in the filtered table.

#### Step 3 - Convert array to actual values

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(SUBTOTAL(103, OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1)), Table1[Number])

becomes

IF({0; 1; 1; 0; 1; 1; 0; 1}, Table1[Number])

and returns

{FALSE; 2; 1; FALSE; 1; 2; FALSE; 2}

#### Step 4 - Match values to condition specified in cell C12

IF($C$12=(IF(SUBTOTAL(103, OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1)), Table1[Number])), MATCH(ROW(Table1[Number]), ROW(Table1[Number])), "")

becomes

IF({FALSE; 2; 1; FALSE; 1; 2; FALSE; 2}, MATCH(ROW(Table1[Number]), ROW(Table1[Number])), "")

becomes

IF({FALSE; 2; 1; FALSE; 1; 2; FALSE; 2}, {1;2;3;4;5;6;7;8}, "")

and returns

{""; ""; 3; ""; 5; ""; ""; ""}

#### Step 5 - Extract k-th smallest row number

To be able to return a new value in a cell each I use the SMALL function to extract row numbers from smallest to largest.

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF($C$12=(IF(SUBTOTAL(103, OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1)), Table1[Number])), MATCH(ROW(Table1[Number]), ROW(Table1[Number])), ""), ROWS($A$1:A1))

becomes

SMALL({""; ""; 3; ""; 5; ""; ""; ""}, ROWS($A$1:A1))

becomes

SMALL({""; ""; 3; ""; 5; ""; ""; ""}, 1)

and returns 3.

#### Step 6 - Return value based on row number

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX(Table1[Result], SMALL(IF($C$12=(IF(SUBTOTAL(103, OFFSET(Table1[Number], MATCH(ROW(Table1[Number]), ROW(Table1[Number]))-1, 0, 1)), Table1[Number])), MATCH(ROW(Table1[Number]), ROW(Table1[Number])), ""), ROWS($A$1:A1)))

becomes

INDEX(Table1[Result], 3)

becomes

INDEX({"A10"; "A20"; "A30"; "A40"; "A50"; "A60"; "A70"; "A80"}, 3)

and returns A30 in cell B15.

### Vlookup and return multiple values category

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

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

VLOOKUP and return multiple matches based on many criteria.

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]

Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]

VLOOKUP a multi-column range and return multiple values.

This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]

Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### One Response to “VLOOKUP in a filtered Excel Table and return multiple values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[...] Vlookup visible data in a table and return multiple values in excel [...]