+ Reply to Thread
Results 1 to 13 of 13

Vlookup data by pulling below row.

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Cambodia
    MS-Off Ver
    2016
    Posts
    118

    Vlookup data by pulling below row.

    Hello All,

    I want C column lookup the date received from I column and chose the lastes received like OS22962 we received 3 times but the last time was on 18 Mar

    The problem is table I lookup is extract from system and like thousand row, so it toke too much time to edit 1 by 1.anyway if I use vlookup the value will turn to 0 becuz received date are below 1 row from mathing information.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Vlookup data by pulling below row.

    Please see the file attached..

    You will have to arrange the data properly though./.

    Hope it helps..
    Attached Files Attached Files
    Cheers!
    Deep Dave

  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,893

    Re: Vlookup data by pulling below row.

    Yes - it would be much easier if you tidied up your data. here's another way of getting the last date
    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

  4. #4
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Cambodia
    MS-Off Ver
    2016
    Posts
    118

    Re: Vlookup data by pulling below row.

    i'm going crazy, i can't open ur attached file, it always said i not register or not log in, while i try to log in many time.

    I don't know what to do. I hope I can open ur file and find out how it work, so it might help me alot on my work.

    Thanks to give me some advice if you ever facing problem as mind.

  5. #5
    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,893

    Re: Vlookup data by pulling below row.

    There is a problem on the forum. Send me your email address by PM.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup data by pulling below row.

    You should organize the data like this:

    Data Range
    G
    H
    I
    J
    4
    OS#
    Description
    Rec.date
    Qty
    5
    22958
    22958TRIM1-1319
    2-Jan
    112
    6
    22959
    22959BODY-2182
    10-Feb
    333
    7
    22959
    22959BODY1-2183
    13-Feb
    111
    8
    22959
    22959TRIM-2185
    1-Feb
    22
    9
    22962
    22962BODY-2234
    7-Mar-15
    10
    10
    22962
    22962BODY-2234
    7-Mar-15
    20
    11
    22962
    22962BODY-2234
    18-Mar-15
    30


    Then, to find the most recent date for an item:

    Data Range
    A
    B
    C
    5
    OS#
    Description
    Rec.Date
    6
    22958
    22958TRIM1-1319
    2-Jan
    7
    22959
    22959BODY-2182
    10-Feb
    8
    22959
    22959BODY1-2183
    13-Feb
    9
    22959
    22959TRIM-2185
    1-Feb
    10
    22962
    22962BODY-2234
    18-Mar


    This array formula** entered in C6:

    =MAX(IF((G$5:G$11=A6)*(H$5:H$11=B6),I$5:I$11))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Format in the date style that you desire.

    Copy down as needed.
    Last edited by Tony Valko; 04-02-2015 at 05:49 AM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Vlookup data by pulling below row.

    Quote Originally Posted by Glenn Kennedy View Post
    Yes - it would be much easier if you tidied up your data. here's another way of getting the last date
    Hi Glenn,

    I am not sure if that's returning the right result..

  8. #8
    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,893

    Re: Vlookup data by pulling below row.

    It's not returning the right result ... I emailed the OP. Having a bad day!!

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Vlookup data by pulling below row.

    c2=IF(A6="","",IFERROR(MAX(INDEX((LOOKUP(ROW($G$5:$G$15),ROW($G$5:$G$15)/($G$5:$G$15<>""),$G$5:$G$15)&LOOKUP(ROW($G$5:$G$15),ROW($G$5:$G$15)/($G$5:$G$15<>""),$H$5:$H$15)=$A6&$B6)*($I$5:$I$15),0)),""))
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  10. #10
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Cambodia
    MS-Off Ver
    2016
    Posts
    118

    Re: Vlookup data by pulling below row.

    Hello Siva,

    I try your formula but it the result FALSE. Can you help to do it on my file and send me with attached file.

    Thanks you so much.

  11. #11
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Cambodia
    MS-Off Ver
    2016
    Posts
    118

    Re: Vlookup data by pulling below row.

    Hello Tony,

    I would tiny my data too but you know it is about 16k row, which I try to do it since yesterday it not still not completed yet. That y I would like a formular which can set up without tiny up the data.

    Thanks




    Quote Originally Posted by Tony Valko View Post
    You should organize the data like this:

    Data Range
    G
    H
    I
    J
    4
    OS#
    Description
    Rec.date
    Qty
    5
    22958
    22958TRIM1-1319
    2-Jan
    112
    6
    22959
    22959BODY-2182
    10-Feb
    333
    7
    22959
    22959BODY1-2183
    13-Feb
    111
    8
    22959
    22959TRIM-2185
    1-Feb
    22
    9
    22962
    22962BODY-2234
    7-Mar-15
    10
    10
    22962
    22962BODY-2234
    7-Mar-15
    20
    11
    22962
    22962BODY-2234
    18-Mar-15
    30


    Then, to find the most recent date for an item:

    Data Range
    A
    B
    C
    5
    OS#
    Description
    Rec.Date
    6
    22958
    22958TRIM1-1319
    2-Jan
    7
    22959
    22959BODY-2182
    10-Feb
    8
    22959
    22959BODY1-2183
    13-Feb
    9
    22959
    22959TRIM-2185
    1-Feb
    10
    22962
    22962BODY-2234
    18-Mar


    This array formula** entered in C6:

    =MAX(IF((G$5:G$11=A6)*(H$5:H$11=B6),I$5:I$11))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Format in the date style that you desire.

    Copy down as needed.

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Vlookup data by pulling below row.

    see the attached file
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Cambodia
    MS-Off Ver
    2016
    Posts
    118

    Re: Vlookup data by pulling below row.

    I still got the problem, I can not download the file. please help me.

    I pending my work since yesterday

    Quote Originally Posted by nflsales View Post
    see the attached file

+ 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. macro for spotting text from one list to another, then pulling in data with vlookup
    By cwchan220 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2014, 11:54 PM
  2. VLookup Data not pulling unless I physically retype the name?
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-23-2013, 05:55 PM
  3. Need help with Vlookup / Pulling from another sheet.
    By Funkis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2013, 06:17 PM
  4. Pulling a Specific Cell when Pulling a Tabel from Web Based Data
    By Zallen89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2013, 05:39 PM
  5. Vlookup Formula Help For Pulling Data From a Tab
    By donnydorko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2009, 10:02 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