VBA Lookup Help - Please

[haas786]

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!

 

[p45cal]

No one else has replied yet so I thought I'd post my not-so-simple offering.

Assuming:
Cells B2, C2, B17:B24 referred to below are all on sheet2.
There's a table on Sheet1 with headers on row1, say:
Name/Deal Type/Deal Number
in columns A,B and C respectively. and this table is populated from row2 to
row 16.

Then on Sheet2, cell B17, array-enter (using Shift+Ctrl+Enter) the following
formula:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),1))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),1)))

If this has been entered properly, when you look at the formula it should
have curly braces around it viz. {=theformula}.

In the same way, enter this formula into B18:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),2))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),2)))

and this in cell B19:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),3))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),3)))

in B20:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),4))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),4)))

in B21:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),5))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),5)))

on B22:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),6))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),6)))

in B23:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),7))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),7)))

in B24:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),8))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1!$B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),8)))

(There are only two small differences in each case)

Obviously, you should amend the ranges to suit or use named ranges:

B17:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),1))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),1)))

B18:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),2))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),2)))

B19:
=IF(ISERROR(INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),3))),"-",INDIRECT("Sheet1!C"
&SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType),ROW(DealNumbers)),3)))

etc. (array-entered, of course)

Named ranges were:
Broker =Sheet2!$B$2
BrokerNames =Sheet1!$A$2:$A$16
DealNumbers =Sheet1!$C$2:$C$16
DealType =Sheet2!$C$2
DealTypes =Sheet1!$B$2:$B$16

I'm still pondering on something simpler (vba would be simpler but I've been
trying to do it with formulae only, (and now I've only just noticed the
subject of the post "VBA Lookup Help"! I'll try to post something later)

 

[p45cal]

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(ResultArray)
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

 

[haas786]

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(ResultArray)
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










- Show quoted text -

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