Author: Oscar Cronquist Article last updated on February 14, 2018

In this article I´ll show you how to lookup two or more values in a list and return (if possible) multiple matches.

The picture below shows you a table in column B and C, the search criteria is in column B and the results are in column G.

I am not using VLOOKUP at all in this array formula, the VLOOKUP looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.

The VLOOKUP function is not designed to look for multiple values and return multiple values.

Array formula in E7:

=INDEX($C$3:$C$7, SMALL(IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1)))

Recommended article

Smallest greater than condition

Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 […]

Smallest greater than condition

How to enter an array formula

  1. Copy the aray formula above (Ctrl + c)
  2. Double click cell G3
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter
  6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=array_formula)}

Don't enter these characters yourself, they appear automatically.