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.