Author: Oscar Cronquist Article last updated on August 26, 2019

Picture of how to extract multiple values based on a condition using an array formula

The VLOOKUP function is designed to return only a corresponding value of the first instance of a lookup value, from a column you choose. But there is a workaround to identify multiple matches.

The array formulas demonstrated below are smaller and easier to understand and troubleshoot than the useful VLOOKUP function.

However you are not limited to array formulas, Excel also has built-in features that work very well, you will be amazed at how easy it is to filter values in a data set.

Table of Contents

  1. VLOOKUP - Return multiple values [vertically]
  2. VLOOKUP - Return multiple values [horizontally]
  3. VLOOKUP - Extract multiple records based on a condition
  4. Lookup and return multiple values [AutoFilter]
  5. Lookup and return multiple values [Advanced Filter]
  6. Lookup and return multiple values [Excel Defined Table]
  7. Return multiple values vertically or horizontally [UDF]
  8. How to count VLOOKUP results
  9. Lookup and return multiple values in one cell

I have made a formula, demonstrated in a separate article, that allows you to VLOOKUP and return multiple values across worksheets, there is also an Add-In that makes it even easier to accomplish this task.

Now, if you only need one instance of each returned value then check this article out: Vlookup – Return multiple unique distinct values It lets you specify a condition and the formula is not even an array formula.

I have also written an article about searching for a string (wildcard search) and return corresponding values, it requires a somewhat more complicated formula but don't worry, you will find an explanation there, as well.

Did you know that it is also possible to VLOOKUP and return multiple values distributed over several columns, the formula even ignores blanks.

VLOOKUP - Return multiple values vertically