Menu
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.
213
To filter a data in an array formula (to exclude or require certain values), you can use an array formula based on the IF, MATCH, and ISNUMBER functions. where "data" is the named range B4:D11 and "filter" is the named range F4:F6. Note: this is an array formula and must be entered with control + shift + enter.
The FILTER function in Excel is used to filter a range of data based on the criteria that you specify. The function belongs to the category of Dynamic Arrays functions. The result is an array of values that automatically spills into a range of cells, starting from the cell where you enter a formula.
If you're using Excel 2013 or any other latest version then you can combine FILTER and ISFORMULA functions together to filter cells containing formulas.
=VLOOKUP(A2,IF(B1:B3="B",A1:C3,""),1,FALSE)
Ctrl+Shift+Enter to enter.
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
