2

I have a table that is like such called "Stocks"

That has a list of tickers across the top row, dates going down Col A and prices in each column for the stocks.

Image

and it goes on for 15 years.

On another sheet call it (Trades) I want a user to enter say "ABC" into a cell and on that table it will pull the price for specific dates that on Trade sheet.

I want to be able to do an index match with a dynamic variable so that a user can enter any symbol and the prices will come up. I have a match formula which gets me the column the ticker is in but can't get this into a index/match formula

=MATCH(E1,SPY!A1:D1,0)
Ram
  • 3,092
  • 10
  • 40
  • 56
Paul Costa
  • 23
  • 2

1 Answers1

2

here is a you can use match in both the column reference and the row reference; that said lets say you have the dates in column A. and the date they are looking up in c1 you can do =Index(STOCKS,MATCH($C$1,Stocks[[Date]:[Date]],0),match($E$1,STocks[#Headers],0)) and that should do what you are looking for.

JamTay317
  • 1,017
  • 3
  • 18
  • 37
  • This works but when I changed the ticker symbol it does not change. =INDEX(Stocks!B3:B3925,MATCH(B12,Stocks!A3:A3925,0),MATCH($E$1,Stocks!$B$1:$I$1,0)) I am using $E$1 for the input of the ticker symbol. – Paul Costa Aug 11 '15 at 19:01