+ Reply to Thread
Results 1 to 6 of 6

Dynamically Sized VLOOKUP Table Array

  1. #1
    Registered User
    Join Date
    07-05-2022
    Location
    Washington
    MS-Off Ver
    365
    Posts
    3

    Dynamically Sized VLOOKUP Table Array

    Hi all. Thanks for the help in advance.
    I've struggled with this one for a while. I'm basically parsing out a registry key and putting all the information on a single row. But the number of items under a header is variable and where the specific one i'm looking for is in a variable position. So what I've been doing is doing a VLOOKUP for that value. What I'm looking for help with is to make the table array variable in size based on a cell with the number of rows for that item.
    For example the lookup is two columns (C & D), and we'll say we're on row 100. The row size cell says 5 so I need the table_array to be C100:D104 (specifically =VLOOKUP("Item",C100:D104,2,FALSE). Basically, I only want to look so far down; I need the final cell (D104 in this case) to be dependent on another cell.

    Again, thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Dynamically Sized VLOOKUP Table Array

    This probably won't be exactly what you need but should get you on the right lines:

    =INDEX(D100:D200,MATCH("Item",C100:INDEX(C100:C200,5),0))

    Ranges D100:D200 and C100:C200 can be adjusted as appropriate but need to be the same size. The 5 near the end will relate to row 104 (the 5th cell when starting at 100), but can be linked to a cell reference if that it easier. Or you can use another MATCH function to define it if that is desired.

    If you want a more tailored solution, please provide a sample workbook - instructions are at the top of the page in the yellow banner.

  3. #3
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Dynamically Sized VLOOKUP Table Array

    Then you need something like:

    Please Login or Register  to view this content.
    Replace the 5 with the cell reference that contains the row size.

    WBD
    Office 365 on Windows 11, looking for rep!

  4. #4
    Registered User
    Join Date
    07-05-2022
    Location
    Washington
    MS-Off Ver
    365
    Posts
    3

    Re: Dynamically Sized VLOOKUP Table Array

    Worked perfectly! Thank you so much!!

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,468

    Re: Dynamically Sized VLOOKUP Table Array

    SamMoore welcome to the forum.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Dave

  6. #6
    Registered User
    Join Date
    07-05-2022
    Location
    Washington
    MS-Off Ver
    365
    Posts
    3

    Re: Dynamically Sized VLOOKUP Table Array

    SOLVED

    Thanks

+ 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. Create a dynamically sized table based on named range
    By jakeruby in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2018, 07:11 PM
  2. [SOLVED] Array formulae with different sized array, e.g. array-if() or somproduct-if()?
    By hlhans in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2016, 03:59 PM
  3. Replies: 1
    Last Post: 03-21-2016, 05:41 AM
  4. [SOLVED] Dynamically sized menu in customised Ribbon tab
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-11-2013, 10:09 PM
  5. Using VLOOKUP with a dynamically created array.
    By PaddyDarby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2010, 04:42 PM
  6. creating a subtotal for a dynamically sized column
    By Galoredk in forum Excel General
    Replies: 2
    Last Post: 03-02-2010, 06:22 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