+ Reply to Thread
Results 1 to 3 of 3

Formula to match a value and pull the next value in the next row, same column of the match

  1. #1
    Registered User
    Join Date
    09-18-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    31

    Formula to match a value and pull the next value in the next row, same column of the match

    Hello,

    I want a formula to take the value of a cell 5 rows above in the same sheet, find the matching value in a different sheet, and return the value of the cell directly below it. I need this to repeat for several rows. Sort of like a counter in a formula.

    For example,

    I want Cell A5 to take the value of A1, find it in column B of sheet 1, and return the value of the cell below it.
    Then I want cell A10 to take the value of A5, find it in the column B of sheet 1, and return the value of the cell below it. And so On and so Forth for several hundred rows.

    EDIT: Index Match seems to work. But I'm open to your suggestions anyway...

    Thanks!
    Last edited by atdemeo; 09-18-2015 at 01:13 AM.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to match a value and pull the next value in the next row, same column of the m

    INDEX MATCH would be the way foward
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula to match a value and pull the next value in the next row, same column of the m

    Try this... although there may be an error in your original requirement... 1 to 5 is a gap of 4, 5 to 10 a gap of 5, so I'm not sure which interval is corect. Adjust the ranges to suit your data.

    =INDEX(Sheet2!$A$1:$A$40,MATCH(Sheet1!A1,Sheet2!$A$1:$A$40,0)+1) in A5 (a gap of 4 rows)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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: 1
    Last Post: 09-09-2015, 10:30 AM
  2. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  3. Match data on two sheets and pull identifer value from a separate column
    By kmrevolution in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2014, 08:43 PM
  4. Match names in two separate column if = then pull info in 3 column
    By Bevg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2013, 08:31 PM
  5. Match to columns in different sheets and pull the 3rd column values
    By Pats83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2013, 11:45 AM
  6. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  7. Replies: 0
    Last Post: 01-23-2012, 02:49 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