Copying rows and columns from a table to a separate sheet based on a ticker

[jrh0311]

Good afternoon,

I've spent most of today attempting to automatically copy a number of rows of data from a table into a separate sheet. The idea is that I enter a ticker into a cell and the formula will retrieve the data from the different rows and columns in the table. VLOOKUP does the job, but only for the first row of course. Being that I desire to pull not just the first entry, but all of the entries with the same ticker, I need something that can get the subsequent rows as well.

An example of the table is below. VLOOKUP would pull the top row just fine, but I am at a loss for how to automatically copy the additional rows.

[TABLE="width: 804"]
<tbody>[TR]
  [TD="class: xl73, width: 83"]Settle[/TD]
  [TD="class: xl72, width: 149"]Security[/TD]
  [TD="class: xl72, width: 82"]Coupon[/TD]
  [TD="class: xl72, width: 106"]Issue Spread[/TD]
  [TD="class: xl72, width: 126"]Current Spread[/TD]
  [TD="class: xl72, width: 129"]Amount[/TD]
  [TD="class: xl70, width: 78"]Maturity[/TD]
  [TD="class: xl71, width: 51"] Years [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 804"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]9/18/2013[/TD]
[TD]VZ 0 09/15/16[/TD]
[TD]FLOATING[/TD]
[TD]#N/A Field Not Applicable[/TD]
[TD="align: right"]49.3[/TD]
[TD] $         2,250,000,000[/TD]
[TD] 9/15/2016 [/TD]
[TD]            3[/TD]
[/TR]
[TR]
[TD]11/7/2012[/TD]
[TD]VZ 3.85 11/01/42[/TD]
[TD]FIXED[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]191.0[/TD]
[TD] $         1,250,000,000[/TD]
[TD] 11/1/2042 [/TD]
[TD]         30[/TD]
[/TR]
[TR]
[TD]11/7/2012[/TD]
[TD]VZ 2.45 11/01/22[/TD]
[TD]FIXED[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]91.6[/TD]
[TD] $         1,750,000,000[/TD]
[TD] 11/1/2022 [/TD]
[TD]         10[/TD]
[/TR]
[TR]
[TD]11/7/2012[/TD]
[TD]VZ 1.1 11/01/17[/TD]
[TD]FIXED[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]41.2[/TD]
[TD] $             500,000,000[/TD]
[TD] 11/1/2017 [/TD]
[TD]            5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
John

 

[jrh0311]

I found the solution, and in case anyone saw this and has had a similar problem here's my approach...

Step 1. Add new column in the database with the following formula:

=IF([@Ticker]=Tearsheet!$D$2,COUNTIF($D$3:D1421,Tearsheet!$D$2),0)

This formula counts all of the occurrences where the ticker I input into cell Tearsheet!D2 matches the ticker in the @Ticker column. For instance, the first entry in the database of the VZ ticker would be 1, the second instance would be 2 and so forth, anything that's not VZ, both before and after, would be 0.

Now that each individual entry of the VZ ticker has a unique (if temporary) number associated with it, VLOOKUP works fine.

Step 2. Add the VLOOKUP formula where you want the information displayed:

=IF(ISERROR(VLOOKUP($J39,Table1[#All],7,FALSE)),"",VLOOKUP($J39,Table1[#All],7,FALSE))

This formula searches the table beginning with that new column we created and adds the desired information, using the number present in $J39 (I filled Column J with numbers from 1-50 to use in the VLOOKUP formula to match with the table) to find the corresponding entry in the table.

Using these two formulas I can now pull the data I need automatically simply by entering a ticker into D2 in the Tearsheet