## Vlookup a cell range and return multiple values

*Article last 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.

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 […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

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 […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

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 […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]

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 comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form