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.
 
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
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
636
MARZIOTULLIO
M
kristell
  • Solved
Duplicate values on different tabs
Replies
3
Views
182
kristell
kristell
D
  • Question Question
Data Validation List without duplicates
2 3 4
Replies
30
Views
1K
kvsrinivasamurthy
K
R
  • Solved
#NAME Error in Image hyberlink
2
Replies
16
Views
357
Ramadan
R
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
545
etaf
etaf

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