+ Reply to Thread
Results 1 to 9 of 9

If Text is within range, return value & skip number of rows when dragging out formula

  1. #1
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    If Text is within range, return value & skip number of rows when dragging out formula

    Hi, Ill try to be short.

    I need a formula to check if the value of Cell S3 is found in B24:33 and if it is, I want my formula to return the value of Cell E34.

    Now when I drag this formula down (Extend it) I want it to Next check B35:B44 for the value of cell S4 and if it is, return the value of Cell E45

    And so on and so on. so basicall, the range is 10 rows then skips 1 row Which is the value I want returned but the valuecheck needs to move only once.

    Hopefully that makes sense, if not let me know and ill try to build a testfile. EDIT: created a testfile.

    Thank you in advance!
    Attached Files Attached Files
    Last edited by Vihral; 04-27-2018 at 04:07 AM.

  2. #2
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    I do have a somewhat simpler alternative that might work. Let me know if this could make it easier!

    if I just do =K34 (Where another calculation is residing which contains the same number). Is there a way to just make it so that when I drag down on that formula (=K34 might not be a formula but you get the point!) it just skips directly to K45, then K56, then K67 etc etc etc? I tried some =Index stuff but could not for the life of me figure it out!

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    if anyone else wants to try a formula they would need to build a test file themselves.....so you might as well build the test file
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    Quote Originally Posted by humdingaling View Post
    if anyone else wants to try a formula they would need to build a test file themselves.....so you might as well build the test file
    Good point, added a testfile now, thanks!

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    what happens if it doesnt find anything?

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    heres your formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    if it doesnt find anything it does NONE
    change as needed
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    Quote Originally Posted by humdingaling View Post
    heres your formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    if it doesnt find anything it does NONE
    change as needed
    Not sure if im doing this wrong, tried regular Enter and ctrl shift but when I pull down on the formula everything just moves 1 row down and it doesnt skip anything. Am I missing something?

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    You cannot drag the formula down as it will overwrite anything in the intervening cells: you can only copy/paste,

    Try

    in K34

    =IFERROR(IF(MATCH($K33,$S$4:$S$13,0),$E34),"")

    Then copy to K45. K56 etc

  9. #9
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    Re: If Text is within range, return value & skip number of rows when dragging out formula

    Quote Originally Posted by JohnTopley View Post
    You cannot drag the formula down as it will overwrite anything in the intervening cells: you can only copy/paste,

    Try

    in K34

    =IFERROR(IF(MATCH($K33,$S$4:$S$13,0),$E34),"")

    Then copy to K45. K56 etc
    See, it was something I did wrong! 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. How to skip rows when dragging a formula
    By casparschwa in forum Excel General
    Replies: 3
    Last Post: 10-02-2016, 06:00 PM
  2. [SOLVED] How to skip columns whilst dragging a formula
    By Saint_8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-25-2014, 08:44 PM
  3. skip 1 cell when dragging down formula
    By excelhelpneeds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2014, 01:30 PM
  4. Replies: 9
    Last Post: 03-06-2014, 04:48 PM
  5. How do you skip a row when auto dragging a formula?
    By gmatula89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 01:08 PM
  6. [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
  7. Skip every other line while dragging formula
    By dlswa in forum Excel General
    Replies: 2
    Last Post: 03-05-2012, 09:31 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