On sheet “data” I have tickers going down Column A, and monthly dates going across Row 1. The rest of the sheet contains the corresponding prices, so cell D4 is AAPL’s price as of 3/31/05, and E4 is AAPL’s prices as of 4/30/05, and E5 is ABC’s price as of 4/30/05. Lots of prices here. On sheet “Results” I have tickers going down Column A, and various dates going down Column B. In C4, I need a formula which pulls the price for the ticker in A4 (AAPL) as of the date in B4. So two variables. What would that formula be? A mix of hlookup and vlookup, connected with AND? thanks!
Try a MATCH function - match the date in B4 to the dates in your “data” tab, and you’ll get a relative number returned (i.e. Feb/05 will be 2, Mar/05 will be 3, etc); then use the VLOOKUP on your “Results” tab, column C, referencing your “data” tab. Within the VLOOKUP, your “column position” will be the entire MATCH formula, rather than a static number. That should do the trick.
=index(data!$1:$65536,match(A1,data!$A:$A,FALSE),MATCH(B1,data!$1:$1,FALSE)) should do it. edit: typo
You can use the sumproduct function for this: lets say that your ‘data’ sheet has 100 rows of data (assuming 101 total rows including one header) and 26 total columns (the fist row being your tickers) in C4 of your ‘Results’ sheet you would enter the formula: =Sumproduct((‘Data’!$A$2:$A$101 = ‘Results’!$A4)*(‘Data’!$B$1:$Z$24 = ‘Results’!$B4)*(‘Data’!$B$2:$Z$101)) You can then drag that formula all the way down column C and you should be good. Best, TheChad
I think SUMIF is more elegant. Try SUMIF(DateRange,Date,Prices) where DateRange is the range of dates i.e. row 1 on Data, Date is the date you are looking up i.e. B4 on results, and Prices is the range of prices ie. row 4 on data. Get your $s right, copy down and you’re done.