Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

OK so I know I can do this with VBA but I'm interested if this is possible without....

Say I have a table like this with some tickers:

Page   |   Ticker
----------------------
1      |   UKX index
1      |   SPX index
2      |   usgg10yr index
3      |   cesiusd index
3      |   cesijpy index
3      |   gukgin10 index
4      |   GSPG10YR  Index

On a separate sheet I have a page number, say 1. I want to get all the tickers from the table above that correspond with the page number of the sheet. So basically filter the table but put the results in another sheet and leave the original table unchanged.

Obviously vlookup/index-match can do this for when there is only one occurrence of a page number but I need it to be able to return several tickers for a given page number (e.g. 2 for page 1, 1 for page 2, 3 for page 3 etc)

Edit: For clarity I would have secondary worksheets that look like this:

Lookup Value: 1

Tickers:
UKX index
SPX index

So for the example above, had i just used vlookup/index-match it would have given me only "UKX index" as it's the first one in the list. I want a method that returns all matches.

share|improve this question

migrated from stackoverflow.com Oct 16 '12 at 20:48

This question came from our site for professional and enthusiast programmers.

    
So you want this "Core" table to have all the Worksheet (Page) references for said information? What is controlling this "Core" tables data? Where would the information that will populate the "Secondary" worksheets come from? –  GoldBishop Oct 16 '12 at 10:57
    
This sounds like pivot tables might be a good fit for you. You're essentially trying to run a query on the data, which is what pivot tables are all about. –  Stewbob Oct 16 '12 at 11:40
    
Sure but I need to reference a cell in the worksheet to provide a lookup value. With pivottables you need to set the filters manually –  harryg Oct 16 '12 at 11:56
1  
If you want to avoid doing things manually, how can you avoid vba? PivotTables are pretty simple. –  Daniel Cook Oct 16 '12 at 14:19
    
Yup, I figure I have to use VBA. I was just wondering if there was a simpler vlookup-like way of doing it –  harryg Oct 16 '12 at 15:25

2 Answers 2

I agree with all the comments above but this is possible just by using a array formula, providing the table is sorted by Page. A formula like below will do it:

=OFFSET(Table,MATCH(Page,PageList,0)-1,1,COUNTIF(PageList,Page),1)

Where:

  • Table is the entire table in your example.
  • Page is the page to filter for.
  • PageList is just the first column of Table.
share|improve this answer

If Pages are in A2:A100 and Tickers in B2:B100 then with a specific Page in D2 you can use this "array formula" in D3

=IFERROR(INDEX(B$2:B$100,SMALL(IF(A$2:A$100=D2,ROW(A$2:A$100)-ROW(A$2)+1),ROWS(D$3:D3))),"")

confirm with CTRL+SHIFT+ENTER and copy down. When you run out of matches you get blanks

For the above I assumed everything on one sheet for simplicity but the same formula can be used to reference data on another sheet, add sheet names where appropriate. Doesn't need data in any particular order

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.