Inconsistent Formula when linking to a different page

**protocoledu** · 11-19-2009, 07:03 AM

Hi there, i've been having problem with this formula every time i changed a certain aspect;

The Current formula currently looks like this;

=IF($B3=0,0,INDEX('Dta Sht'!$D$1:$D$36000,MATCH(1,($B3='Dta Sht'!$A$1:$A$36000)*((VLOOKUP(C$1,$AC:$AD,2,))='Dta Sht'!$C$1:$C$36000),0)))

And all I wanted to do was a simply change in the vlookup part to this;

=IF($B3=0,0,INDEX('Dta Sht'!$D$1:$D$36000,MATCH(1,($B3='Dta Sht'!$A$1:$A$36000)*((VLOOKUP(C$1,Dta!$K$1:$L$67,2,))='Dta Sht'!$C$1:$C$36000),0)))

I do have to press ctrl, shift & enter to usually get this formula to work but it just won't accept the new vlookup values, any help would be appreciated!

**DonkeyOte** · 11-19-2009, 07:16 AM

You don't outline what the problem is with your revised formula specifically - ie the error, however, I dread to think what the performance must be like... an Array referencing over 100,000 cells

If I may make a suggestion ?

On 'Dta Sht' find a blank column, in that column (say Z for demo) enter:

Z1: =$A1&"@"&$C1
copy down to Z36000

Now change your Array formula to

=IF($B3=0,0,INDEX('Dta Sht'!$D$1:$D$36000,MATCH($B3&"@"&VLOOKUP(C$1,Dta!$K$1:$L$67,2,),'Dta Sht'!$Z$1:$Z$36000,0)))
entered as normal with ENTER

Based on your cell references ($Bx, y$1) the implication is that you are building some sort of matrix, if so the above applied to said matrix in preference to the Array approach should lead to significant improvement in performance.

**protocoledu** · 11-19-2009, 07:54 AM

Sorry i didn't include the error i received for my 2nd formula!

The reason I include so many rows is that the data always changes and the way to always make it work I just include all rows so whatever data I ended up having it would always work!

Thanks for your formula but it never seemed to work and I think I know why!

with your formula i can see what you're trying to do with the =$A1&"@"&$C1, but my original data is somewhat structured. I've attached a sample of what the data looks like and what I'm trying to achieve;

I'm looking for in cells J6/7/8 to show the 'Adverts' values from column D, but looking up the cells I6/7/8 against O3:P5 to find the actual row to look up in Column D (which would be on a different sheet).

Does that make sense of what i'm trying to achieve?

**DonkeyOte** · 11-19-2009, 08:21 AM

I don't think you're showing the whole picture, after all your current Array formula is an Array because it has two conditions on the horizontal match (ie B3:A and VLOOKUP:C) whereas in your sample is there is seemingly only one horizontal match (VLOOKUP:C).
On that basis it's hard to pass comment given the sample file does not mirror the requirements outlined in your OP, for ex. in your sample file it's a simple:

J5: =INDEX($D$8:$D$17,MATCH(VLOOKUP($I5,$O$2:$P$4,2,),$C$8:$C$17,0))

Regards your range - I would suggest investigating use of Dynamic Named Ranges if you intend to persist with inefficient Arrays so as to minimise damage as much as possible.

Regards not using concatenation we'll have to agree to disagree - nothing mentioned / illustrated as yet precludes it as far as I can tell.

**protocoledu** · 11-19-2009, 12:50 PM

Donkeyote thanks for your help in this!

I'm not too sure about his formula myself I know it just works when i want it to!

I've attached a better spreadsheet which explains the kind of data that I get. I have many different offices to look up and due to different amounts of boards in column C that formula has always worked, its just when I change the small vlookup function within the formula it just decides not too work! I'm just wondering why it kicks it out when I've only changed the simplest part of the formula??

**DonkeyOte** · 11-19-2009, 01:03 PM

OK, that makes more sense... to illustrate process of concatenation using your most recent upload...

G1: =$A1&":"&$C1
copied down
(you need not use G -- this is purely for demo)

you can then dispense with expensive arrays altogether, eg:

J6: =INDEX(D:D,MATCH($I$5&":"&VLOOKUP($I6,$O$2:$P$4,2,0),$G:$G,0))
copied down to J8

The above can be applied to remaining blocks but given your setup (re: Office name) the reference to $I$5 would need to be updated accordingly - ie to $I$11, then $I$16 ...

However, if you were to place this Office Criteria in a different column to Board Criteria you could use a formula which would not require alteration, eg

J5: Office: Daves Office
(and do likewise transferring I11,I18 to J11,J18)

you could then use

J6: =INDEX(D:D,MATCH(LOOKUP(REPT("Z",255),$J$5:$J5)&":"&VLOOKUP($I6,$O$2:$P$4,2,0),$G:$G,0))

and copy that to all result cells without need for alteration

(worth adding the above could also be copied right to K/L to get Responses / Ratio values)