Looking up data in a large sheet

[gbirdsong]

I have two sheets of data. Sheet A will have three pieces of
information (Account Number, Ticker, Shares) and Sheet B across the
top lists accounts and below lists tickers (up to 1400).

Sheet A (example)

1000, XXX, 1
2000, YYY, 1


Sheet B (example)

1000, 1500, 2000
AAA, AAA, BBB
CCC, CCC, CCC
XXX, XXX, XXX

In column D I need to look up in Sheet B if for Account 1000 XXX is
listed below etc.

Any help will be appreciated.

 

[RadarEye]

I have two sheets of data. Sheet A will have three pieces of
information (Account Number, Ticker, Shares) and Sheet B across the
top lists accounts and below lists tickers (up to 1400).

Sheet A (example)

1000, XXX, 1
2000, YYY, 1

Sheet B (example)

1000, 1500, 2000
AAA, AAA, BBB
CCC, CCC, CCC
XXX, XXX, XXX

In column D I need to look up in Sheet B if for Account 1000 XXX is
listed below etc.

Any help will be appreciated.

Hi Gbirdsong,

You could try something like this for cell D2 on SheetA:

' =IF(ISNA(MATCH(B2;INDIRECT("SheetB!"&ADDRESS(1;MATCH(A2;SheetB!
$1:$1;0))&":"&ADDRESS(100;MATCH(A2;SheetB!$1:$1;0)));0));"Not
listed";"Listed")

If you would like to know in which row the ticker is listed try:

=MATCH(B2;INDIRECT("SheetB!"&ADDRESS(1;MATCH(A2;SheetB!
$1:$1;0))&":"&ADDRESS(100;MATCH(A2;SheetB!$1:$1;0)));0))

But in this case if a tickercode is not listen the result will be "N/
A"

HTH,

Wouter

 

[Dan R.]

A formula would be the easiest way but if you need a macro try this:

Sub Test()
Dim i As Range, iEnd As Long, iRng As Range
Dim found1 As Variant, found2 As Variant

With Sheets(1)
iEnd = .Cells(Rows.Count, 1).End(xlUp).Row
Set iRng = .Range(.Cells(1, 1), .Cells(iEnd, 1))
End With

For Each i In iRng
Set found1 = Sheets(2).Range("A1:IV1").Find( _
i.Value, LookIn:=xlValues)
If found1 Is Nothing Then
i.Offset(0, 3) = "Not Found"
Else
With Sheets(2)
ActCol = Mid$(found1.Address, 2, 1) & ":" & _
Mid$(found1.Address, 2, 1)
End With

Set found2 = Sheets(2).Range(ActCol).Find( _
i.Offset(0, 1).Value, LookIn:=xlValues)

If found2 Is Nothing Then
i.Offset(0, 3) = "Not Found"
Else
i.Offset(0, 3) = "Found"
End If
End If
Next i

End Sub