Filter an array using a formula (without VBA)

Question

Is it possible to filter an array using a single formula (without autofilter, VBA, or additional columns)?

For example, I have the following spreadsheet:

   A  | B     | C
 --------------------
1| ID | Class | Value
2| 1  | A     | V1
3| 1  | B     | V2
4| 2  | A     | V3
5| 3  | B     | V4
6| 3  | B     | V5

I want to use a subset of this array in VLOOKUP. Namely, I only want to match against those rows where class is "B". So I was hoping I could use something like the following

=VLOOKUP(A3, FILTER_FUNC(A:C, B="B"), 3, false)

where FILTER_FUNC is some type of function or expression that returns an array that only contains those rows that meet the condition.

Answer 1

=VLOOKUP(A2,IF(B1:B3="B",A1:C3,""),1,FALSE)

Ctrl+Shift+Enter to enter.

Answer 2

This will do it if you only want the first "B" value, you can sub a cell address for "B" if you want to make it more generic.

=INDEX(A2:A6,SUMPRODUCT(MATCH(TRUE,(B2:B6)="B",0)),1)

To use this based on two columns, just concatenate inside the match:

=INDEX(A2:A6,SUMPRODUCT(MATCH(TRUE,(A2:A6&B2:B6)=("3"&"B"),0)),1)

Answer 3

Sounds like you're just trying to do a classic two-column lookup. http://www.dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/

Tons of solutions for this, most simple is probably the following (which doesn't require an array formula):

=SUMPRODUCT((Lookup!A:A=Param!A1)*(Lookup!B:B=Param!B1)*(Lookup!C:C))

To translate your specific example, you would use:

=SUMPRODUCT((A1:A3=A2)*(B1:B3="B")*(C1:C3))

Answer 4

Today, in Office 365, Excel has so called 'array functions'. The filter function does exactly what you want. No need to use CTRL+SHIFT+ENTER anymore, a simple enter will suffice.

In Office 365, your problem would be simply solved by using:

=VLOOKUP(A3, FILTER(A2:C6, B2:B6="B"), 3, FALSE)