Why? Vlookup glitch using dynamic named range

[IanTaylor]

Hi All,

Can anyone explain this, when I use the below formula in columns A-K this formula evaluates perfectly, but when in column L, I get a #ref error with respect to the dynamic named range AnalysisA. If i replace the named range with a cell range it works fine. Just strikes me as weird.

=VLOOKUP($B9&$J9,AnalysisA,6,FALSE)

Any thoughts would be appreciated.


Ian

 

[GlennUK]

Post the definition of the name AnalysisA. ( am not a mindreader )

 

[IanTaylor]

Glitch sorted!

Glen,

AnalysisA is defined as;

=OFFSET(AnalysisA!$A$3,0,0,COUNTA(AnalysisA!$A:$A),COUNTA(AnalysisA!$3:$3))

Actually found the problem, I had not fixed the A:A and the 3:3 so for whatever reason this would not evaluate properly whenused in teh look up, it just so happens that Analysis A is K rows wide and I guess this is where I was going wrong.

Cheers,
Ian