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 G3:

=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

Lookup using two criteria in excel

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

Comments(0) Filed in category: Excel, Vlookup

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.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

How to copy formula to cells below

  1. Select cell G3
  2. Copy cell (Ctrl + c)
  3. Select cell range G4:G5
  4. Paste (Ctrl + v)

How the array formula works in cell G3

You can easily follow a long as I explain the array formula, download the workbook. Select cell B13, go to tab "Formulas". Click on "Evaluate formula" button.

Click on "Evaluate" button show above to move to next step.

Step 1 - Count matching search criteria in column B

COUNTIF($E$3:$E$4, $B$3:$B$7)

becomes

COUNTIF({"US";"Asia"},{"US";"Europe";"US";"Europe";"Asia"})

and returns {1;0;1;0;1}

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

Step 2 - Convert boolean array into corresponding row numbers

IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF({1;0;1;0;1}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF({1;0;1;0;1}, {1;2;3;4;5}, "")

and returns {1;"";3;"";5}

These are the row numbers that correspond to the matching values US and Asia in column B.

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 3 - Returns the k-th smallest row number

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))

becomes

SMALL({1;"";3;"";5}, ROWS($A$1:A1))

becomes

SMALL({1;"";3;"";5}, 1)

and returns 1.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

Step 4 - Return a value based on coordinate

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)))

becomes

INDEX($C$3:$C$7, 1)

becomes

INDEX({"Pen";"Eraser";"Paper";"Pen";"Paper clip"}, 1)

and returns Pen in cell G3.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Download excel *.xlsx file

Vlookup with multiple search conditions and return multiple matches.xlsx