Dynamic table array within Vlookup

[Relentless]

Hello and thank you in advance for your help! I've referenced this site many times and generally can find the answer to my questions but its just not coming together for me this time!

I'm simply trying to create a dynamic formula for the table-array within a vlookup. I have a large array with years worth of daily stock quotes for dozens of stocks. Unfortunately, the dates do not align for each ticker quote so within the array there are two columns for each stock symbol, one for the date and the other for the quote. Rather than spending the time to align the dates (that get constantly updated), I was attempting to create a vlookup that identified the right ticker and appropriate columns to return the correct stock price.

Here is tiny snapshot of the data set:

Column =>	BO	BS	BT	BU	BV	BW	BX	BY
	BRK B Date	BRK B Price	KMP Date	KMP Price	AHS Date	AHS Price	BHP Date	BHP Price
	2/8/11	$84.27	1/20/11	$71.23	2/25/11	$7.53	4/1/11	$81.11
	2/9/11	$83.89	1/21/11	$71.88	2/28/11	$7.47	4/4/11	$82.00
	2/10/11	$84.45	1/24/11	$72.70	3/1/11	$7.27	4/5/11	$82.41
	2/11/11	$84.91	1/25/11	$72.59	3/2/11	$7.16	4/6/11	$83.40
	2/14/11	$85.30	1/26/11	$72.87	3/3/11	$7.47	4/7/11	$83.04

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Here is the formula that isn't working (in bold where it fails)

=IF(TODAY()<$B81,"",IFERROR(VLOOKUP($B81, ADDRESS(4,MATCH(G4,A3:SS3,0)) & ":" & ADDRESS(5000,MATCH(G4,A3:SS3,0)+1),MATCH(G$4,$BO$3:$SS$3,0),TRUE),""))

I was using the two address functions to create the range for the table-array. By themselves the two address functions return: $BX$4 and $BY$5000 which is the range I want for that particular cell. However, I can't get vlookup to accept the output of the address functions as the range. I've tried playing with index and indirect functions which is where I think this is going but Its either not appropriate or I'm not using the correct syntax.

I could have probably manually re-input every variable in the array with the amount of time I've spent on this but now its personal! lol, I want to make it work!

Thank you for your assistance

 

[Marcelo Branco]

Welcome to Mr Excel

Assuming data in columns BO:SS, headers in row 3, if i understand what you are looking for, i.e.:
Given a date in B81 (for example 4/4/11) and a header in G4 (for example BHP Date), the formula should return the price (BHP Price) = $82.00

Maybe this
<b81,"",vlookup(b81,index(bo:ss,0,match(g4,bo3:ss3,0)):index(bo:ss,0,match(g4,bo3:ss3,0)+1),2,0))
=IF(TODAY() < B81,"",VLOOKUP(B81,INDEX(BO:SS,0,MATCH(G4,BO3:SS3,0)):INDEX(BO:SS,0,MATCH(G4,BO3:SS3,0)+1),2,0))

Hope this helps

M.</b81,"",vlookup(b81,index(bo:ss,0,match(g4,bo3:ss3,0)):index(bo:ss,0,match(g4,bo3:ss3,0)+1),2,0))

 

[Squat]

Hi Marcelo,

I had an identical problem and the formula worked perfectly.
[h=3][/h]Best regards,

 

[Marcelo Branco]

Hi Marcelo,

I had an identical problem and the formula worked perfectly.
Best regards,

I'm glad it helped

M.