On May 13, 9:54 am, p45cal wrote:
Following on, a VBA solution:

In a standard module:

Sub blah()
Dim ResultArray()
ReDim ResultArray(1 To 1)
counter = 0
For Each cll In Range("BrokerNames")
If cll.Value = Sheets("Sheet2").Range("B2") And _
UCase(cll.Offset(0, 1).Value) = Sheets("Sheet2").Range("c2") Then
counter = counter + 1
ReDim Preserve ResultArray(1 To counter)
ResultArray(counter) = cll.Offset(0, 2).Value
End If
If counter = 8 Then Exit For 'to stop searching if 8 have been found
Next cll
mySize = UBound(ResultArray) - LBound(ResultArray)
Sheets("Sheet2").Range("B17:B24").ClearContents
Sheets("Sheet2").Range("B17:B" & 17 + mySize) = _
Application.WorksheetFunction.Transpose(ResultArra y)
End Sub

In Sheet2's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Or Target.Address = "$C$2" Then
blah
End If
End Sub

It requires the Named range:
BrokerNames =Sheet1!$A$2:$A$16

--
p45cal



" wrote:
Hi all! I desparately need help with an Excel problem. Here's an
example of what I'm trying to achive below:

In Cell B2 I have a drop down list which contains names of our
brokers.
In Cell C2 I have a drop down list describing types of deals (values
are C, A, X, or T)

Once the user chooses the name and type of deal, they would press a
button which would then take the values in B2 and C2 together and
find
matches in another sheet which contains 3 columns worth of data:
Name,
Type of Deal, and Deal Number (alphanumeric). There will never be 0
deal numbers or
more than 8 answers for any given Criteria (e.g. If I select Tom and
C, the resulting answer will have between 1 and 8 different Deal
Numbers.)

Once Excel or VBA finds these deal numbers, they need to be "placed"
or copied into cells B17 all the way down to B24 (if there are 8
deals
numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
Excel will copy both deal numbers and paste them into B17 and B18.

I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
this but if possible, please let me know.

Thank you in advance for your help!- Hide quoted text -

- Show quoted text -

Thank you all for yur help - you guys are amazing!