Vlookup query for different sheet involving: INDIRECT, SUBSTITUTE, ADDRESS

[Zaineph]

Hi all,

I'm trying to create formula that will lookup a range of cells in a different sheet. The table array need to be from 2 columns, however I need the table array to be dynamic so that I can fill in other cells. I've arrange the table arrays next to each other on a seperate sheet called 'Fund Prices' the first array is in columns B:C. The next array is in E:F, then H:I and so on.

I've also created a reference in column A of my active sheet to help with the lookup. So far in cell C6 on my active sheet I have the following:

=IFERROR(VLOOKUP($F$4,INDIRECT("'Fund Prices'!" & SUBSTITUTE(ADDRESS(A6,A6+1+(2*(A6-1)),4),A6," ") & ":" & SUBSTITUTE(ADDRESS(A6,A6+2+(2*A6-1)),4),A6," ")),2,FALSE),0)

The value in cell A6 is "1". So Evaluating, it should be something like:

=IFERROR(VLOOKUP($F$4,'Fund Prices'!B:C,2,FALSE,0)

However when I evaluate the formula I get to:

=IFERROR(VLOOKUP(44099,INDIRECT("'Fund Prices'!B:C "),2,FALSE),0)

One evaluation step further gives

=IFERROR(VLOOKUP(44099,#REF!,2,FALSE),0)

I may be going about this the wrong way or I may be misunderstanding the INDIRECT function but any help would be appreciated.

[Limor_OP]

Hello,
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[Zaineph]

Hi thank you.

Hopefully I've attached this correctly. I've highlighted the cell on the 'Background calc' tab. The below cells are what they should actually reference so when we fill down they should remain the same.

Thank you again

[Limor_OP]

Hi

You should lose the space where I marked red, just replace it with ""

=IFERROR(VLOOKUP($F$4,INDIRECT("'Fund Prices'!" & SUBSTITUTE(ADDRESS(A6,A6+1+(2*(A6-1)),4),A6," ")&":" & SUBSTITUTE(ADDRESS(A6,A6+2+(2*(A6-1)),4),A6," ")),2,FALSE),0)