Filter an array using a formula (without VBA)

excel worksheet-function
J. Christian picture J. Christian · Jun 27, 2011 · Viewed 127.2k times · Source

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

GSerg picture GSerg · Jun 27, 2011 
=VLOOKUP(A2,IF(B1:B3="B",A1:C3,""),1,FALSE)

Ctrl+Shift+Enter to enter.

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More