FILTER As Alternative To VLOOKUP, INDEX MATCH & XLOOKUP Functions

Introduction

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to use FILTER Dynamic Array Function to perform the same operation that lookup family functions such as VLOOKUP, INDEX MATCH and XLOOKUP will perform. Let’s get started

Definition of FILTER Function & Syntax

The FILTER Function filters a range or an array

Syntax             =FILTER(array, include, [if_empty])

Sample Data

In the caption below, we have a sample data that we are going to use to achieve the lookups

We want to populate column E with the corresponding country from the lookup table/table array in columns J and K based on the lookup values in column B. In this first example, we will deploy the VLOOKUP Function.

VLOOKUP Function Calculation

  • In cell E4, execute the formula: =VLOOKUP(B4,$J$4:$K$75,2,0).

B4 is the relative cell reference of the VLOOKUP lookup_value. $J$4:$K$75 is the absolute cell reference of the second argument which is table_array. 2 represent the column index number and 0 forces the VLOOKUP to perform Exact Match

  • Click Enter an copy down the formula.

In the caption below, we can see the corresponding countries returned based on the lookup values and table array.

INDEX/MATCH Functions Calculation

  • In cell F4, execute the formula: =INDEX($K$4:$K$75,MATCH(B4,$J$4:$J$75,0)).

$K$4:$K$75 is the absolute cell reference we want to return which is the INDEX function array argument. MATCH(B4,$J$4:$J$75,0) is the row number argument of the INDEX function

  • Click Enter an copy down the formula

In the caption below, we have the same result that VLOOKUP returned

XLOOKUP Function Calculation

  • In cell G4, execute the formula: =XLOOKUP(B4,$J$4:$J$75,$K$4:$K$75)

B4 is the lookup_value. $J$4:$J$75 is the absolute cell reference for the XLOOKUP lookup_array. $K$4:$K$75 is the absolute cell reference for the XLOOKUP return_array argument

  • Click Enter and copy down the formula

In the caption below, we have the same result

FILTER Function Calculation

We have seen how to use the lookup family functions to lookup values. We want to see how to use the FILTER function to achieve the same result

  • In cell H4, execute the formula: =FILTER($K$4:$K$75,$J$4:$J$75=B4)

$K$4:$K$75 is the absolute cell reference of FILTER function array which we want to return

$J$4:$J$75=B4 is the include argument input values

  • Click Enter and copy down the formula.

In the caption below, we have the same result

Conclusion

We have seen how to use different functions in Excel to achieve the same result. That is the beauty of working with Excel; there are myriads of ways to achieve the same outcome.

You can watch the video below and subscribe to my YouTube Channel if you haven’t do so!

Posted in Blog and tagged , , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *