Results 1 to 2 of 2

Thread: Vlookup with specific row count
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup with specific row count

    Hi, is it possible to combine vlookup with get value in specific row. Do i need to do macro behind or it can be done with cell formula only. When using vlookup only value in first row retrieve.Example as
    =VLOOKUP(B4,B3:C9,2,FALSE)
    In scenario below, i want to get payment date result based on payment received, such for example for ID 123 which suppose go to cell second payment date instead of first date payment.

    Any help is appreciated.


    DATA

    Sheet1
    Name ID Payment Date
    Mike 123 01/01/2002
    Jason 111 02/01/2002
    Mike 123 05/02/2002
    Suzy 222 16/03/2002
    Mike 123 25/03/2002
    Suzy 222 08/04/2002
    Sheet2
    Name ID Payment Received
    Mike 123 2
    Jason 111 1
    Suzy 222 2

    OUTCOME

    Report
    123 01/01/2002

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup with specific row count

    this will work if the payment dates are in order as shown

    ABC
    1Sheet1
    2NameIDPayment Date
    3Mike12301/01/2002
    4Jason11102/01/2002
    5Mike12305/02/2002
    6Suzy22216/03/2002
    7Mike12325/03/2002
    8Suzy22208/04/2002
    9
    10Sheet2
    11NameIDPayment Received
    12Mike1232
    13Jason1111
    14Suzy2222
    15
    16
    17OUTCOME
    18
    19Report
    2012305/02/2002
    2111102/01/2002
    2222208/04/2002

    Sheet1



    Array Formulas
    CellFormula
    B20{=SMALL(IF($B$3:$B$8=A20,$C$3:$C$8),C12)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •