Vlookup to Return Multiple entries ...... HELP!!!!!!

[TD Rich]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am looking for a way to have the vlookup function (or an equivalent) return multiple entries.<o></o>
<o></o>
I have a name in cell A1 and I want to search for that name in column A on another sheet and then return the corresponding value in column C every time this name is found.<o></o>
<o></o>
Any help would be greatly appreciated as this has been driving me mad for hours now!!!!!!!!! <o></o>
<o></o>
Over to you Mr.Excel !!<o></o>
<o></o>
Cheers,<o></o>
<o></o>
Rich.<o></o>

 

[sulakvea]

hi, and welcome to the board. is VBA an option?

 

[TD Rich]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Hello sulakvea,<o></o>
<o> </o>
<o> </o>
<o> </o>
Yes, although I am very much a beginner when it comes to VBA I have been using it for this project (mostly by recording macros and the copying / editing / pasting other bits of VBA code that I have managed to alter to suit what I am trying to achieve). <o></o>
<o> </o>
<o> </o>
<o> </o>
Cheers,<o></o>
<o> </o>
<o> </o>
<o> </o>
Rich.<o></o>

 

[sulakvea]

although, one way would be to just write this in C1, and copy down. keep the @ signs where i placed them:

=if(A1=$A$1,A1,"")

here's a VBA equivalent:

Sub FindAllValues()

i = 1
With Sheet1.Range("A:A")
 Set c = .Find(Sheet1.Range("A1"), LookIn:=xlValues, lookat:=xlWhole)
 If Not c Is Nothing Then
  firstAddress = c.Address
  Do
   c.Offset(0, 2).Value = c.Value
   i = i + 1
   Set c = .FindNext(c)
  Loop While Not c Is Nothing And c.Address <> firstAddress
 End If
End With
  
End Sub

 

[TD Rich]

Thanks sulakvea,

Really apreciate that. Thats a massive help!

Cheers,

TD Rich

 

[TD Rich]

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=310 border=0 x:str><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 124pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=165 height=17>



Sheet2

A
tonwship Resources Ltd.
</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=74>





B
mr man
</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num>





C
2.00
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>tonwship Resources Ltd.</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">mrs woman</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>3.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>tonwship Resources Ltd.</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">mr maple</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>4.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>stupid macro company</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">mr canada</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>5.00</TD></TR></TBODY></TABLE>



Sheet1

A
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0 x:str><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 124pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=165 height=20>tonwship Resources Ltd.</TD></TR></TBODY></TABLE>

What i am looking to do is have excel look at text in Cell A1 on sheet1 and then look for corresponding entries in column A on sheet 2. Then for every corresponding entry it finds on sheet 2 return the value in column B of that sheet to Cell B1 on sheet 1. Basicly Vlookup but returning not just the first entry that it finds but all entries that match.

Cheers,

Rich.