Lookup with duplicate values.

V

vishnu.yadav

New Member
Joined
Jan 22, 2010
Messages
9
Hi Friends,
I have my data arranged in a table which appears to be like this:

File ID QA
3803 Vishnu
4006 Shalini
3803 Akanksha
5034 Sanchit
5034 Shalini
3803 ****iz

I wants multiple results here from lookup where I need all QA names for a give File ID. When I am trying using VLOOKUP, I get to see only the first name where as I need all the names. For instance when I search for File ID 3803, I should get the names of Vishnu, Akanksha & ****iz, in different cells. Please help. Thanks in advance.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hello,

You could do this with the following set up:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"><COL style="WIDTH: 19px"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD><TD></TD><TD>File ID:</TD><TD style="TEXT-ALIGN: right">3803</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>Vishnu</TD><TD></TD><TD>Count:</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">4006</TD><TD>Shalini</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>Akanksha</TD><TD></TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5034</TD><TD>Sanchit</TD><TD></TD><TD style="TEXT-ALIGN: right">3803</TD><TD>Vishnu</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5034</TD><TD>Shalini</TD><TD></TD><TD style="TEXT-ALIGN: right">3803</TD><TD>Akanksha</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>****iz</TD><TD></TD><TD style="TEXT-ALIGN: right">3803</TD><TD>****iz</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=COUNTIF(A2:A7,E1)</TD></TR><TR><TD>D5</TD><TD>=IF(E5="","",E$1)</TD></TR><TR><TD>E5</TD><TD>{=IF(ROWS(E$5:E5)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E5))),"")}</TD></TR><TR><TD>D6</TD><TD>=IF(E6="","",E$1)</TD></TR><TR><TD>E6</TD><TD>{=IF(ROWS(E$5:E6)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E6))),"")}</TD></TR><TR><TD>D7</TD><TD>=IF(E7="","",E$1)</TD></TR><TR><TD>E7</TD><TD>{=IF(ROWS(E$5:E7)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E7))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Simple copy down D5 and E5 far enough to cover the maximum possible number of names that could be returned through looking up a given File ID.

Does this help?

Matty
 
Upvote 0
Thanks Matty for your help...but this doesn't seems to be working for me. The moment I changed File ID from 3803 to 5034, the result obtained was not accurate.

Also, I believe the solution given by you will only will only work if the lookup value is a number, but what if it is text. Need a solution for that as well.

Regards,
Vishnu Yadav
 
Upvote 0
The moment I changed File ID from 3803 to 5034, the result obtained was not accurate.
Click to expand...

Works OK for me. See:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"><COL style="WIDTH: 19px"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD><TD></TD><TD>File ID:</TD><TD style="TEXT-ALIGN: right">5034</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>Vishnu</TD><TD></TD><TD>Count:</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">4006</TD><TD>Shalini</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>Akanksha</TD><TD></TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5034</TD><TD>Sanchit</TD><TD></TD><TD style="TEXT-ALIGN: right">5034</TD><TD>Sanchit</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5034</TD><TD>Shalini</TD><TD></TD><TD style="TEXT-ALIGN: right">5034</TD><TD>Shalini</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3803</TD><TD>****iz</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=COUNTIF(A2:A7,E1)</TD></TR><TR><TD>D5</TD><TD>=IF(E5="","",E$1)</TD></TR><TR><TD>E5</TD><TD>{=IF(ROWS(E$5:E5)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E5))),"")}</TD></TR><TR><TD>D6</TD><TD>=IF(E6="","",E$1)</TD></TR><TR><TD>E6</TD><TD>{=IF(ROWS(E$5:E6)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E6))),"")}</TD></TR><TR><TD>D7</TD><TD>=IF(E7="","",E$1)</TD></TR><TR><TD>E7</TD><TD>{=IF(ROWS(E$5:E7)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E7))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I believe the solution given by you will only will only work if the lookup value is a number, but what if it is text.
Click to expand...

Not true. See:

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"><COL style="WIDTH: 19px"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD><TD></TD><TD>File ID:</TD><TD>Text1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">Text1</TD><TD>Vishnu</TD><TD></TD><TD>Count:</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Text3</TD><TD>Shalini</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">Text1</TD><TD>Akanksha</TD><TD></TD><TD style="FONT-FAMILY: Verdana">File ID</TD><TD>QA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana">Text2</TD><TD>Sanchit</TD><TD></TD><TD>Text1</TD><TD>Vishnu</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana">Text2</TD><TD>Shalini</TD><TD></TD><TD>Text1</TD><TD>Akanksha</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana">Text1</TD><TD>****iz</TD><TD></TD><TD>Text1</TD><TD>****iz</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=COUNTIF(A2:A7,E1)</TD></TR><TR><TD>D5</TD><TD>=IF(E5="","",E$1)</TD></TR><TR><TD>E5</TD><TD>{=IF(ROWS(E$5:E5)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E5))),"")}</TD></TR><TR><TD>D6</TD><TD>=IF(E6="","",E$1)</TD></TR><TR><TD>E6</TD><TD>{=IF(ROWS(E$5:E6)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E6))),"")}</TD></TR><TR><TD>D7</TD><TD>=IF(E7="","",E$1)</TD></TR><TR><TD>E7</TD><TD>{=IF(ROWS(E$5:E7)<=E$2,INDEX(B$2:B$7,SMALL(IF(A$2:A$7=E$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(E$5:E7))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Are you committing the formula in E5 with Ctrl+Shift+Enter, as it's an array formula? This would need doing before copying down.

Matty
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Complex Lookup issue
Replies
1
Views
146
arthurbr
arthurbr
R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
623
awoohaw
awoohaw
R
  • Question
SUMIFS between dates where dataset has multiple entries
Replies
2
Views
186
Fluff
Fluff
J
  • Solved
Return multiple deliminated values from lookup
Replies
2
Views
88
jimbomcmucka
J
U
  • Question
Lookup/Vlookup to find exact date
Replies
6
Views
173
UndwaterExcelWeaver
U

Forum statistics

Threads
1,214,636
Messages
6,120,666
Members
448,977
Latest member
moonlight6

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top