VLOOKUP with duplicates

See:

http://www.mrexcel.com/forum/showthread.php?t=231882

Thanks Aladin, I'll check it out and post back if I get stuck. But at a quick glance it seems as though you've thoroughly explained it - THANKS again!

Aladin,

I used your logic in the above link and worked well, the next hurdle was being able to produce a unique list in order to display the results under them. I used a previous post you helped me with to get the unique entries list. The below is the completed solution. The credit does go to you as all the solutions in the below example were formulated by you - so once again THANKS!

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>NAME</TD><TD style="TEXT-ALIGN: center">SUM</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Noah</TD><TD style="TEXT-ALIGN: center">333</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Adam</TD><TD style="TEXT-ALIGN: center">555</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: center">777</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Noah</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: center">400</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Rebecca</TD><TD style="TEXT-ALIGN: center">700</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Noah</TD><TD style="TEXT-ALIGN: center">Adam</TD><TD style="TEXT-ALIGN: center">Sarah</TD><TD style="TEXT-ALIGN: center">Rebecca</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">333</TD><TD style="TEXT-ALIGN: center">555</TD><TD style="TEXT-ALIGN: center">777</TD><TD style="TEXT-ALIGN: center">700</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD style="TEXT-ALIGN: center">400</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>{=SUM(IF(FREQUENCY(IF(A3:A8<>"",MATCH("~"&A3:A8&"",A3:A8&"",0)),ROW(A3:A8)-ROW(A3)+1),1))}</TD></TR><TR><TD>A10</TD><TD>{=IF(ROWS($A$10)<=$A$1,INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",MATCH("~"&$A$3:$A$8&"",$A$3:$A$8,0)),ROW($A$3:$A$8)-ROW($A$3)+1),ROW($A$3:$A$8)-ROW($A$3)+1),ROWS($B$9:B9))),"")}</TD></TR><TR><TD>B10</TD><TD>{=IF(ROWS($A$10)<=$A$1,INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",MATCH("~"&$A$3:$A$8&"",$A$3:$A$8,0)),ROW($A$3:$A$8)-ROW($A$3)+1),ROW($A$3:$A$8)-ROW($A$3)+1),ROWS($B$9:B10))),"")}</TD></TR><TR><TD>C10</TD><TD>{=IF(ROWS($A$10)<=$A$1,INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",MATCH("~"&$A$3:$A$8&"",$A$3:$A$8,0)),ROW($A$3:$A$8)-ROW($A$3)+1),ROW($A$3:$A$8)-ROW($A$3)+1),ROWS($B$9:B11))),"")}</TD></TR><TR><TD>D10</TD><TD>{=IF(ROWS($A$10)<=$A$1,INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",MATCH("~"&$A$3:$A$8&"",$A$3:$A$8,0)),ROW($A$3:$A$8)-ROW($A$3)+1),ROW($A$3:$A$8)-ROW($A$3)+1),ROWS($B$9:B12))),"")}</TD></TR><TR><TD>A11</TD><TD>=COUNTIF($A$2:$A$8,A10)</TD></TR><TR><TD>B11</TD><TD>=COUNTIF($A$2:$A$8,B10)</TD></TR><TR><TD>C11</TD><TD>=COUNTIF($A$2:$A$8,C10)</TD></TR><TR><TD>D11</TD><TD>=COUNTIF($A$2:$A$8,D10)</TD></TR><TR><TD>A12</TD><TD>{=IF(ROWS(A$12:A12)<=A$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=A$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(A$12:A12))),"")}</TD></TR><TR><TD>B12</TD><TD>{=IF(ROWS(B$12:B12)<=B$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=B$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(B$12:B12))),"")}</TD></TR><TR><TD>C12</TD><TD>{=IF(ROWS(C$12:C12)<=C$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=C$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(C$12:C12))),"")}</TD></TR><TR><TD>D12</TD><TD>{=IF(ROWS(D$12:D12)<=D$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=D$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(D$12:D12))),"")}</TD></TR><TR><TD>A13</TD><TD>{=IF(ROWS(A$12:A13)<=A$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=A$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(A$12:A13))),"")}</TD></TR><TR><TD>C13</TD><TD>{=IF(ROWS(C$12:C13)<=C$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=C$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(C$12:C13))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

I had the same problem and before I posted the question I searched the message board and receved the answer in the second thread that I read.

So it does pay to search first.

Thanks to shemayisroel for asking the question originally and special thanks to Aladin for the answer.

Hi,


Sorry to bring up an old post.

Re my post # 4, instead of names in Column A, what if I had dates? How would I modify my formula say in A12 that is currently working but when you change the names to dates it's returning #NUM!

In A12

{=IF(ROWS(A$12:A12)<=A$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=A$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(A$12:A12))),"")}

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>NAME</TD><TD style="TEXT-ALIGN: center">SUM</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01/01/09</TD><TD style="TEXT-ALIGN: center">333</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>02/01/09</TD><TD style="TEXT-ALIGN: center">555</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>07/01/09</TD><TD style="TEXT-ALIGN: center">777</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01/01/09</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>07/01/09</TD><TD style="TEXT-ALIGN: center">400</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>03/01/09</TD><TD style="TEXT-ALIGN: center">700</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">01/01/09</TD><TD style="TEXT-ALIGN: center">02/01/09</TD><TD style="TEXT-ALIGN: center">07/01/09</TD><TD style="TEXT-ALIGN: center">03/01/09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">333</TD><TD style="TEXT-ALIGN: center">555</TD><TD style="TEXT-ALIGN: center">777</TD><TD style="TEXT-ALIGN: center">700</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD style="TEXT-ALIGN: center">400</TD></TR></TBODY></TABLE>

shemayisroel said:

Hi,


Sorry to bring up an old post.

Re my post # 4, instead of names in Column A, what if I had dates? How would I modify my formula say in A12 that is currently working but when you change the names to dates it's returning #NUM!

In A12

{=IF(ROWS(A$12:A12)<=A$11,INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=A$10,ROW($B$3:$B$8)-ROW($B$3)+1),ROWS(A$12:A12))),"")}

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>NAME</TD><TD style="TEXT-ALIGN: center">SUM</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01/01/09</TD><TD style="TEXT-ALIGN: center">333</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>02/01/09</TD><TD style="TEXT-ALIGN: center">555</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>07/01/09</TD><TD style="TEXT-ALIGN: center">777</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01/01/09</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>07/01/09</TD><TD style="TEXT-ALIGN: center">400</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>03/01/09</TD><TD style="TEXT-ALIGN: center">700</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">01/01/09</TD><TD style="TEXT-ALIGN: center">02/01/09</TD><TD style="TEXT-ALIGN: center">07/01/09</TD><TD style="TEXT-ALIGN: center">03/01/09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">333</TD><TD style="TEXT-ALIGN: center">555</TD><TD style="TEXT-ALIGN: center">777</TD><TD style="TEXT-ALIGN: center">700</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">200</TD><TD></TD><TD style="TEXT-ALIGN: center">400</TD></TR></TBODY></TABLE>

Click to expand...

Dates should not make any difference at all and they don't...

Ignore me, so obvious - dates!!! Were not true dates.

Hi All,

Sorry for bringing this topic again, but I have a question here.

I have an excel sheet with 2 sheets viz. sheet 1 and sheet 2. In both sheets there are 2 columns A and B. In sheet 1 I made a drop- down list (not filter) in column A, B, C, ...... with 100 values each, which are also present in column A of sheet 2.

What I need is whenever 1 choose a value from drop down list of column A of sheet1, a drop down list should be made to the corresponding cell in column B in sheet2. The values that has to be present in column B should be taken from sheet 2.

I couldn't get the answer from above posts. Can you plz help me out here?

Thanks in advance!!!!

Hey I got it!!! Thanks