+ Reply to Thread
Results 1 to 3 of 3

How to skip a cell reference while dragging down a Hlookup formula

  1. #1
    Registered User
    Join Date
    08-05-2013
    Location
    new york city, new york
    MS-Off Ver
    Excel 2011
    Posts
    20

    How to skip a cell reference while dragging down a Hlookup formula

    Hi,

    I have a formula below that I want to drag and also skip every other cell reference.

    A2:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(90:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(90:$1083),FALSE),0))/1000

    So this formula basically looks up a value in row 90 from another her tab and tells me the value.

    Now I want to drag this formula so that A3 and A4 and onwards look like this:
    A3:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(92:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(92:$1083),FALSE),0))/1000

    A4:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(94:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(94:$1083),FALSE),0))/1000

    A5:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(96:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(96:$1083),FALSE),0))/1000

    Please help!

    Thanks,

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to skip a cell reference while dragging down a Hlookup formula

    I used a helper column.

    I set B3 = 92, B4 = 94

    I set A3:="=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW("&B3&":$1083),FALSE)),HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW("&B3&":$1083),FALSE),0))/1000"

    and B3:="=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW("&B4&":$1083),FALSE)),HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW("&B4&":$1083),FALSE),0))/1000"

    Then I selected both and dragged down, letting B count by 2's and the formula incrementing as necessary. After you fill in A3:A? you just select the formulas, copy, paste values, and activate them.

  3. #3
    Registered User
    Join Date
    08-05-2013
    Location
    new york city, new york
    MS-Off Ver
    Excel 2011
    Posts
    20

    Re: How to skip a cell reference while dragging down a Hlookup formula

    Sorry, but I have no idea what you just said. I am a beginner and I dont even know what a helper column is.

    Can you explain it in baby steps?

    Thank you!

+ 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. Dragging formula (cell reference) across columns
    By theraindew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 01:51 PM
  2. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  3. [SOLVED] Skip column when dragging formula.
    By D_N_L in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2012, 07:42 AM
  4. Skip every other line while dragging formula
    By dlswa in forum Excel General
    Replies: 2
    Last Post: 03-05-2012, 09:31 PM
  5. Dragging down formula and cell reference
    By JDCONNER in forum Excel General
    Replies: 4
    Last Post: 09-30-2011, 05:05 PM

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