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

User · Answer

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

User · Answer

VLOOKUP A2 IF B1 B3  B  A1 C3     1 FALSE    Ctrl Shift Enter to enter

User · Answer

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 amp B2 B6    3  amp  B   0   1

User · Answer

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  quot B quot    3  FALSE

[excel] Filter an array using a formula (without VBA)

Examples related to excel

Examples related to worksheet-function