Using VLookup to return multiple instances

T

TK_Hussar

New Member
Joined
Dec 31, 2007
Messages
27
Hi
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for
"A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.

Look forward to hearing from anyone that can help.

Many thanks in anticipation.

Kind regards

TK_Hussar
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hello TK, welcome to MrExcel

Assuming you want to search in A1:A100 and return a value from B1:B100 try this formula in D2, confirmed with CTRL+SHIFT+ENTER and copied down

=IF(ROWS(D$2:D2)>COUNTIF(A$1:A$100,A$123),"",INDEX(B$1:B$100,SMALL(IF(A$1:A$100=A$123,ROW(B$1:B$100)-ROW(B$1)+1),ROWS(D$2:D2))))
 
Upvote 0
TK_Hussar said:
Hi
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for
"A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.

Look forward to hearing from anyone that can help.

Many thanks in anticipation.

Kind regards

TK_Hussar
Click to expand...

Consider the range A2:C10 with the source data in A2A10...

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=229 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>X</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>Lookup Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A26</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"># Occurrences</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=COUNTIF(A2:A10,C2)">3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Cell Addresses</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C6)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C6)))),"")' x:arrayrange="C6">$A$2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B14</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C7)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C7)))),"")' x:arrayrange="C7">$A$5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C8)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C8)))),"")' x:arrayrange="C8">$A$9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(C$6:C9)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C9)))),"")' x:arrayrange="C9"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A125</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

C2 houses the lookup value of interest.

C3:

=COUNTIF(A2:A10,C2)

C6:

Control+shift+enter...

=IF(ROWS(C$6:C6)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C6)))),"")

then copy down.

Addendum. Replace "Address" with "Row" in order to obtain the relevant native row numbers.
 
Last edited:
Upvote 0
Many thanks to all who have responded. I now have the solution I needed and will save us days of effort.

Happy New Year to you all.

TK_Hussar.
 
Upvote 0
Hello
I have been searching for a way to get multiple results from a single reference and the post by Aladin is very close to what I am looking for ... the only difference is that I would like the contents of of the cell next to it instead of the cell reference.

e.g. using the previous example (adapted) - for the value A123, I would the list of results to show Jim, Dave and Fred (rather than A2, A5, A9)

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=229 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>X</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>Lookup Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">jim</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A26</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">paul</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"># Occurrences</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ian</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:fmla="=COUNTIF(A2:A10,C2)" x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">dave</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Cell Addresses</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">carl</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C6)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C6)))),"")' x:arrayrange="C6">$A$2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B14</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">steve</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C7)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C7)))),"")' x:arrayrange="C7">$A$5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">mike</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C8)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C8)))),"")' x:arrayrange="C8">$A$9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">fred</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(C$6:C9)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C9)))),"")' x:arrayrange="C9"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A125</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">pete</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
I hope someone will be able to help, it would be much appreciated
Cheers Sair
 
Upvote 0
Sair said:
Hello
I have been searching for a way to get multiple results from a single reference and the post by Aladin is very close to what I am looking for ... the only difference is that I would like the contents of of the cell next to it instead of the cell reference.

e.g. using the previous example (adapted) - for the value A123, I would the list of results to show Jim, Dave and Fred (rather than A2, A5, A9)

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=229 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>X</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>Lookup Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">jim</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A26</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">paul</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"># Occurrences</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ian</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:fmla="=COUNTIF(A2:A10,C2)" x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">dave</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Cell Addresses</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">carl</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C6)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C6)))),"")' x:arrayrange="C6">$A$2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B14</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">steve</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C7)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C7)))),"")' x:arrayrange="C7">$A$5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A12</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">mike</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS(C$6:C8)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C8)))),"")' x:arrayrange="C8">$A$9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">fred</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(C$6:C9)<=C$4,CELL("Address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$6:C9)))),"")' x:arrayrange="C9"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A125</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">pete</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
I hope someone will be able to help, it would be much appreciated
Cheers Sair
Click to expand...

Change the formula in C6 to:
Code: 
=IF(ROWS(C$6:C6)<=C$4,INDEX($A$2:$A$10,
    SMALL(IF($A$2:$A$10=C$2,ROW($A$2:$A$10)-ROW($A$2)+1),
      ROWS(C$6:C6))),"")
 
Upvote 0
Thank you very much for taking the time to reply, but now I am getting A123 in my "results" rather than the corresponding contents from "column B" - any idea how I can adapt this ?
 
Upvote 0
Sair said:
Thank you very much for taking the time to reply, but now I am getting A123 in my "results" rather than the corresponding contents from "column B" - any idea how I can adapt this ?
Click to expand...

Change the INDEX bit from:

INDEX($A$2:$A$10

to:

INDEX($B$2:$B$10
 
Upvote 0
TK_Hussar,

Function VlookupALLOccurrence(name As String, IntervalSearches As Range, IntervalReturn As Range, Occurrence As Integer)
Dim Nome
Dim k As Integer, i As Integer
Application.Volatile
k = 1
i = 1
For Each Nome In IntervalSearches
If Nome = name Then
If k = Occurrence Then VlookupALLOccurrence = IntervalReturn(i, 1)
k = k + 1
End If
i = i + 1
Next Nome
End Function

=VlookupALLOccurrence($A$2,$A2:$A$9,$B$2:$B$9,1) =Jim
=VlookupALLOccurrence($A$2,$A2:$A$9,$B$2:$B$9,2) =Dave
=VlookupALLOccurrence($A$2,$A2:$A$9,$B$2:$B$9,3) =Fred
=VlookupALLOccurrence($A$2,$A2:$A$9,$B$2:$B$9,4) =0
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
486
nevyoung
N
R
  • Solved
MATCH Formula to Return multiple Row Numbers.
Replies
5
Views
287
Alex Blakenburg
A
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
207
Fluff
Fluff
S
  • Solved
VLOOKUP with IF or LOOKUP - Which is the right one to use?
Replies
5
Views
514
6StringJazzer
6StringJazzer
A
  • Question Question
VBA to update value in VLOOKUP looked up data
Replies
1
Views
60
Joe4
Joe4

Forum statistics

Threads
1,225,072
Messages
6,182,695
Members
453,132
Latest member
nsnodgrass73

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