Dynamic VLOOKUP wit INDIRECT reference

Hi,

I'm trying to get the following to work

A cell contains a VLOOKUP formula like below

Please Login or Register  to view this content.

and shoud therefore display a value from the 3rd column of the H1table named range.

But H1table should be dynamic. The name of the range that vlookup should use is in cell L1.
So I tried

Please Login or Register  to view this content.

but this gives me an error: #REF invalid cell reference and I don't understand why.

How can I get this to work?

Perhaps the table whose name is in L1 is not three columns wide.

Hope this helps.

Pete

quiz v8.xlsm

@Pete, no I think the table format is fine.
@JBeaucaire, see attached.

I think it is because the tables themselves are defined as dynamic using OFFSET - I don't think INDIRECT can work with them as they are two volatile functions.

Hope this helps.

Pete

Ah yes, that's probably it. Too bad those functions won't play nice together.
I tried changing the definition of MarkeringTable to something like =Data!$A$1:$C$11
Now it's giving me #N/A. Did you manage to get it to display a vlookup result, Pete?

---------- Post added at 03:40 PM ---------- Previous post was at 03:39 PM ----------

Originally Posted by JBeaucaire

That is correct, Pete. You cannot INDIRECTly reference a dynamic range.

Supernova, I see no benefit to the dynamic range definition of the MarkerTable. Redefine that range as A:C on that sheet.





===========
EDIT: Hat-tip to NBVC.

Ok, thanks for the clarification. The reason for the dynamic reference is that I want (users to be) able to add more questions to that particular chapter later on. Ofcourse I can define the range as A1:C500 or something like that but it's not as elegant

Originally Posted by supern0va

I tried changing the definition of MarkeringTable to something like =Data!$A$1:$C$11
Now it's giving me #N/A. Did you manage to get it to display a vlookup result, Pete

There is no data in K2, so there is nothing to look up. You can change your formula to this:

=IF(K2="","",VLOOKUP(K2,INDIRECT(L1), 3, FALSE))

with L1 containing MarkeringTable, which I have changed to have the following reference:

='Markering vaarwater symbolen'!$A:$C

Hope this helps.

Pete

Hey, that's weird... I posted a thankyou message a while ago but it didn't appear. Anyhow, problem is solved. So again: thank you!