+ Reply to Thread
Results 1 to 2 of 2

Making HLookup Formula Dynamic

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    10

    Making HLookup Formula Dynamic

    I'm currently using an HLookup formula to create an output tab for a huge set of input data, but whenever I add new rows to the input tab, I have to adjust the formulas for each individual output cell.

    For example, my output formula is currently "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$384,116,FALSE)," but if I add a new row to the input data, I have to adjust the table array and the row number for the formula. So, if I added a row before the 116th row, but wanted the output to be the same for this cell, I would have to update the formula to "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$385,117,FALSE).

    Is there any way to adjust this formula so that it will automatically update to produce the same output without manually updating the formula each time I edit the number of input rows? Would an index/match formula work, and if so, what would it look like?

    Thanks so much!!!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Making HLookup Formula Dynamic

    Please Login or Register  to view this content.
    where a2 could contain the right boundary and a3 the desired row.

    another way is to reference the range as $b:$b but the changing row number would still have to be referenced as a variable

+ 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. Replies: 6
    Last Post: 06-11-2013, 08:32 PM
  2. [SOLVED] SUMIF formula with dynamic column? HLOOKUP?
    By jrvstl in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-01-2012, 12:04 AM
  3. Making a GETPIVOTDATA formula dynamic (different spreadsheets used)
    By VoodooChild in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2010, 11:59 AM
  4. Can HLOOKUP have a dynamic filename?
    By deg in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 03-31-2009, 04:16 PM
  5. Tricky dynamic HLOOKUP (Or better way do to it??)
    By bluepenguin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-05-2007, 06:08 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