VLOOKUP to return multiple values?

[CHC Consulting LLC]

How can I lookup cell A2 of workbook 'Billing Reconciliation' in column C of workbook '2010 REV LOG.XLS' and have it return all of the associated values related to in Column E, not just the first value but ALL values.

Is this possible?

 

[Simon Lloyd]

It's not possible using VLookup, HLookup or Lookup as these will only ever return the first value.

It can only be done with VBA, are all the sheets to work on in the same workbook? will the results be pasted in the same workbook?

 

[CHC Consulting LLC]

They are not in the same workbook now, but can be if they need to. The results will be pasted in the 'Billing Reconciliation' workbook.

Excel 2010 btw.

 

[Simon Lloyd]

Try this but both workbooks must be open and you need to change the workbook names and the sheet names

Sub Macro2()
Dim wb As Workbook, Rng As Range, MyCell As Range
Set wb = Workbooks("testbooka.xls")
Set Rng = wb.Sheets("Sheet1").Range("C1:C" & wb.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell = ThisWorkbook.Sheets("Sheet1").Range("A2") Then
ThisWorkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = MyCell.Offset(0, 2).Value
End If
Next MyCell
End Sub

 

[CHC Consulting LLC]

I will give it a shot... thank you very much!

 

[Simon Lloyd]

testbooka is the workbook you are doing the lookup in so that will be 2010 REV LOG.XLS but you didnt specify sheet names or where you wanted the results which is why you had a generic answer

P.S aren't you a member at my site? (in sig below)

 

[CHC Consulting LLC]

yes, I am a member at your site. Most of the time I make my post at both sites. I got distracted this time and didn't get over to the cage. I like it very much.

 

[Simon Lloyd]

Haha!, it wasn't a plug, i just recognised the screenie!

 

[CHC Consulting LLC]

Haha!, it wasn't a plug, i just recognised the screenie!

LOL, gotcha. I am going to try this code in a few minutes. I am no good at VBA so i might have to make a VBA post later.

Thanks again for your help.

 

[Aladin Akyurek]

How can I lookup cell A2 of workbook 'Billing Reconciliation' in column C of workbook '2010 REV LOG.XLS' and have it return all of the associated values related to in Column E, not just the first value but ALL values.

Is this possible?

Try to adapt post #2 from:

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