+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP with variable table array using INDIRECT and OFFSET

  1. #1
    Registered User
    Join Date
    08-10-2015
    Location
    Montana, USA
    MS-Off Ver
    MS Excel 2010
    Posts
    2

    VLOOKUP with variable table array using INDIRECT and OFFSET

    Say I have a conditional format rule in cell C1 that says =VLOOKUP(B1,INDIRECT(A1),2,FALSE)=C1

    In cell A1, I have a named range, which changes going down the column... A1 = Atablearray, A2 = Btablearray, so forth

    I want to be able to copy the conditional format rule down the column and have the vlookup conditional format formula update with the correct table array for that row.

    I can get this to work if my table arrays are fixed, but when I put in a variable defined table array using OFFSET, it doesn't work anymore. (See my example for conditional format rule in cell D1).

    Any suggestions?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VLOOKUP with variable table array using INDIRECT and OFFSET

    Tablearray names are changed
    AAtablearray as tablearray1
    BBtablearray as tablearray2
    CCtablearray as tablearray3

    Formula for CF in D1
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-10-2015
    Location
    Montana, USA
    MS-Off Ver
    MS Excel 2010
    Posts
    2

    Re: VLOOKUP with variable table array using INDIRECT and OFFSET

    Thank you, but this is not what I am looking for.

    The range names can be changed as you did, but I need them to stay in Column A and be referenced in the Vlookup formula. This is how my data is set up and that can't change. I also want to be using some type of variable defined range name using OFFSET or something like it, if possible.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP with variable table array using INDIRECT and OFFSET

    I didn't download your file.

    INDIRECT won't evaluate named ranges defined using functions like OFFSET.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Variable Table Array VLookup
    By cossie2k in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2015, 06:23 AM
  2. [SOLVED] Can you set array for Vlookup or Index/Match using Offset?
    By inincubus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2014, 12:52 PM
  3. vlookup with variable range and variable array size
    By chaslie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2013, 02:37 PM
  4. [SOLVED] How to offset a vlookup tabe array by x columns
    By trilliansounds in forum Excel General
    Replies: 3
    Last Post: 03-28-2012, 09:27 AM
  5. Variable File Reference and Variable Table Array in VBA VLOOKUP
    By Gingeiko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2011, 11:08 PM
  6. Excel 2007 : Variable Table Array in VLOOKUP
    By andrewryan in forum Excel General
    Replies: 7
    Last Post: 08-26-2011, 01:28 PM
  7. [SOLVED] Variable table array name in VLOOKUP
    By DoctorG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2005, 04:05 PM

Tags for this Thread

Bookmarks

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