## Vlookup a cell range and return multiple values

*Article updated on February 14, 2018*

My database is as shown, where I have company abc sells siemens, omron and mitsubishi and company qwe sells omron n siemens.

Company Products

abc Siemens Omron Mitsubishi

qwe Omron Siemens

asd Omron Moeller

zxc Mitsubishi Omron

So right now I wanna key in the product name, lets say siemens, I hope it shows all the companies that sell siemens, in this case would be abc n qwe.

How is it possible? Using your formula can only works for first column, it will shows company abc only (first column), how to make it seach through the second column n show company qwe as well?

### Answer:

This array formula looks up a value in a range (C3:E6) and returns multiple unique distinct values from a column (B3:B6). Cell C9 is the lookup value.

**Array formula in cell B12:**

#### Watch a video where I explain how it works

Recommended articles

Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

#### How to create an array formula

- Double click on cell B12.
- Copy (Ctrl + c) and paste (Ctrl + v) above array formula to cell B12.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.

Learn the basics of Excel arrays

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

#### How to copy array formula

- Select cell B12
- Copy (Ctrl + c)
- Select cell range B12:B14
- Paste ( Ctrl + v)

**Explaining array formula in cell B12**

**Step 1 - Find matching values in array**

($C$3:$E$6=$C$9)

becomes

{"Siemens", "omron", "mitsubishi";"Omron", "Siemens", 0;"Omron", "Moeller", 0;"mitsubishi", "Omron", 0}="Siemens"

and returns

{TRUE, FALSE, FALSE;FALSE, TRUE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE}

**Step 2 - Remove duplicate values in array**

COUNTIF($B$11:B11, $B$3:$B$6)=0

becomes

COUNTIF("Company:", {"abc";"qwe";"asd";"zxc"})=0

becomes

{0; 0; 0; 0}=0

and returns

{TRUE; TRUE; TRUE; TRUE}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**Step 3 - Return row numbers**

IF(($C$3:$E$6=$C$9)*(COUNTIF($B$11:B11, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, "")

becomes

IF(({TRUE, FALSE, FALSE;FALSE, TRUE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE})*({TRUE; TRUE; TRUE; TRUE}), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, "")

becomes

IF({1, 0, 0;0, 1, 0;0, 0, 0;0, 0, 0}, ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, "")

becomes

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

and returns

{1, "", "";"", 2, "";"", "", "";"", "", ""}

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

**Step 4 - Find smallest row number**

SMALL(IF(($C$3:$E$6=$C$9)*(COUNTIF($B$11:B11, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, ""), 1)

becomes

SMALL({1, "", "";"", 2, "";"", "", "";"", "", ""}, 1)

and returns 1.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**Step 5 - Return a value or reference of the cell at the intersection of a particular row and column**

=INDEX($B$3:$B$6, SMALL(IF(($C$3:$E$6=$C$9)*(COUNTIF($B$11:B11, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, ""), 1))

becomes

=INDEX($B$3:$B$6, 1)

and returns abc in cell B12.

Gets a value in a specific cell range based on a row and column number.

### Download excel sample file

Vlookup a cell range.xls

(Excel 97-2003 Workbook *.xls)

Recommended blog posts

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Unique distinct list sorted alphabetically based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article