CODEWITHSUNDEEP
excelexcel-formulaexcel-match
Paul Costa
Paul Costa

Reputation: 23

Dynamic Column Reference

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)

Upvotes: 2

Views: 1309

Answers (1)

JamTay317
JamTay317

Reputation: 1017

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.

Upvotes: 2

Related Questions