Filter an array using a formula (without VBA)

user_430202 0
Viewed 127782

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.

worksheet-function excel
4 Answers

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)
user_75688660
Related