How to VLOOKUP with multiple conditions?

I have been working & training on MS Excel for quite some time, and VLOOKUP has been one of the most popular formulas. Most of us who deal with data has to figure out ways to add more details in an existing table, especially taking reference from another table. VLOOKUP has a proven track record in this context.

By default, VLOOKUP returns the search result based on one matching column. But at times, we need to match more than one column and then return the expected result.

How can we achieve this without adding any helper column? In modern Excel, there are multiple ways of accomplishing this. In this article, we will be using the FILTER function.

In the past few months, Office 365 has seen the inclusion of array-based formulas. FILTER is one of them. Following is the syntax of the FILTER function:

Decoding FILTER function

FILTER has the following syntax:

FILTER(array,include,[if_empty])

· Array: The range which we need in return

· Include: The column which we would like to filter

· [if_empty] (Optional): Values to return if no matches found

Let’s consider the following example:

We can use the following formula in cell F5 =FILTER(A1:D10,D1:D10=F3,””)

And it returns a table with the matching Status.

Using it with VLOOKUP

Let’s say we need to find only those records associated with the ID where the Status = “Pass”. The VLOOKUP formula is:

=VLOOKUP($F3,FILTER($A$1:$D$10,$D$1:$D$10=”Pass”,””),2,TRUE)

Using FILTER function, we have filtered the array to return only results with Status = “Pass”, and then traditional VLOOKUP formula will do the rest.

We can combine more multiple conditions with * (which works as AND operator)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
@imVivRan

Founder — VIVRAN.IN || BI Consultant || Trainer || Power BI Super User || Power Apps Developer || Excel Expert || www.vivran.in