wackyboy asks:

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 values from a column (B3:B6). Cell C9 is the lookup value.

Array formula in cell B12:

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

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Named ranges

tbl_prd: C3:E6

How to create a named range

  1. Select cell range C3:E6
  2. Type tbl_prd in name box
  3. Press Enter

How to copy array formula

  1. Select cell B12
  2. Copy (Ctrl + c)
  3. Select cell range B12:B14
  4. Paste ( Ctrl + v)

Explaining array formula in cell B12

Step 1 - Find matching values in array

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

(tbl_prd=$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

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

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}

Step 3 - Return row numbers

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

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

becomes

IF(({TRUE, FALSE, FALSE;FALSE, TRUE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE})*({TRUE; TRUE; TRUE; TRUE}), ROW(tbl_prd)-MIN(ROW(tbl_prd))+1, "")

becomes

IF({1, 0, 0;0, 1, 0;0, 0, 0;0, 0, 0}, ROW(tbl_prd)-MIN(ROW(tbl_prd))+1, "")

becomes

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

and returns

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

Step 4 - Find smallest row number

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

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

becomes

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

and returns 1.

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((tbl_prd=$C$9)*(COUNTIF($B$11:B11, $B$3:$B$6)=0), ROW(tbl_prd)-MIN(ROW(tbl_prd))+1, ""), 1))

becomes

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

and returns abc in cell B12.

Download excel sample file for this tutorial.

Vlookup a cell range.xls
(Excel 97-2003 Workbook *.xls)

Recommended blog posts

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test, [value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Related posts:

How to find duplicates in a range spanning multiple columns?

Filter text values existing in range 1 but not in range 2 using array formula in excel

Filter common text values in range 1 and in range 2 using array formula in excel

Filter values existing in range 1 but not in range 2 using array formula in excel

Vlookup with multiple matches returns a different value in excel