Dynamic VLOOKUP wit INDIRECT reference

1. 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?

2. Re: Dynamic VLOOKUP wit INDIRECT reference

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

Hope this helps.

Pete

3. Re: Dynamic VLOOKUP wit INDIRECT reference

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Point out the troublesome formula if it's not obvious.

4. Re: Dynamic VLOOKUP wit INDIRECT reference

quiz v8.xlsm

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

5. Re: Dynamic VLOOKUP wit INDIRECT reference

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

6. Re: Dynamic VLOOKUP wit INDIRECT reference

Just use defined ranges (whole columns)... VLOOKUP is efficient with those.

``Please Login or Register  to view this content.``

7. Re: Dynamic VLOOKUP wit INDIRECT reference

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.

8. Re: Dynamic VLOOKUP wit INDIRECT reference

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

9. Re: Dynamic VLOOKUP wit INDIRECT reference

using the whole column references is just as easy for users to add more questions later..... you don't need to define a definite range with VLOOKUP.

10. Re: Dynamic VLOOKUP wit INDIRECT reference

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

11. Re: Dynamic VLOOKUP wit INDIRECT reference

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!

12. Re: Dynamic VLOOKUP wit INDIRECT reference

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1