Vlookup a cell range and return multiple values
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
Recommended articles
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Recommended articles
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
How to create an array formula
- Double press with left mouse button 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.
Recommended articles
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}
Recommended articles
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, "";"", "", "";"", "", ""}
Recommended articles
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.
Recommended articles
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.
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Vlookup and return multiple values category
This post explains how to lookup a value and return multiple values. No array formula required.
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
VLOOKUP and return multiple matches based on many criteria.
Excel categories
3 Responses to “Vlookup a cell range and return multiple values”
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
Paste image link to your comment.
Hi Oscar,
I am using your formula here to pull some data from a Google Sheet that is linked to a Google Form. How can I tweak the formula to allow duplicates to be shown? The way I have it written right now, as long as the values that match the criteria are different, they are returned. If I have any duplicate values it only returns the first of those.
=INDEX('Form Responses 1'!$D$2:$D$1006, SMALL(IF(('Form Responses 1'!$C$2:$C$1006=$A$1)*(COUNTIF($G$13:G13,'Form Responses 1'!$D$2:$D$1006)=0), ROW('Form Responses 1'!$C$2:$C$1006)-MIN(ROW('Form Responses 1'!$C$2:$C$1006))+1, ""), 1))
My goal is to search the Form Responses for a name that's in a cell and return all of the values in a designated column that match the name. There will be a lot of duplicates and I need those in my data.
I appreciate any advice you can offer! Thank you for putting all of this information out there for folks like me!
Robert,
Based on the example in this article the following formula seems to work:
=INDEX($B$3:$B$6,SMALL(IF(($C$3:$E$6=$C$9),ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1,""),ROWS($A$1:A1)))
Get the Excel file:
Vlookup-a-cell-range-return-duplicates.xlsx
Want to Extract Unique Distinct Value from 5 Different Cell
A J Entp 919825025403 919825025403 919825009822 919825025403 919825025403
A K Cera 919898363600 919898363600 919275017313
Aadi Ceramic 919898708413 919898708413
Aalishan 919825008579 919825008579
Aarti Cera 919157802760 919157802760
Aaryan Cera 919879832474 919879832474
Aatik ti 918347739973 918347739973 918347739973 918347739973 919879832474
Aavkar Trad 919909481398 919909481398
Absolute Floors 919817297172