Vlookup to return the nth value.
Results 1 to 4 of 4

Thread: Vlookup to return the nth value.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup to return the nth value.

    This is my formula that returns the 1st value but I need to return the data from the 2nd and so on. My project is to pull data to populate invoices based on the invoice number.

    =VLOOKUP(value(rngInvoice),Received_Process, 2,false)

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    219
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup to return the nth value.

    I could do this with INDEX and AGGREGATE but if you want to use VLOOKUP then I'll need to add a worker column.

    A B C D E F G
    1 What?= X513
    2
    3 Worker rngInvoice Value Invoice Received
    4 3X512 X512 $ 13.20 X513 $ 33.00
    5 3X412 X412 $ 16.50 X513 $ 23.10
    6 3X513 X513 $ 19.80 X513 $ 19.80
    7 2X513 X513 $ 23.10
    8 1X013 X013 $ 26.40
    9 1X613 X613 $ 29.70
    10 1X513 X513 $ 33.00
    11 0X313 X313 $ 36.30
    VLOOKUP (4)

    Worksheet Formulas
    Cell Formula
    F4 =IF(ROWS($F$3:F4)>(COUNTIF($B$4:$B$25,$G$1)+1),"",VLOOKUP(ROWS($F$3:F4)-1&$G$1,$A$4:$C$20,2,0))
    G4 =IF(ROWS($F$3:F4)>(COUNTIF($B$4:$B$25,$G$1)+1),"",VLOOKUP(ROWS($F$3:F4)-1&$G$1,$A$4:$C$20,3,0))
    A4 =IF(B4="","",COUNTIF($B4:$B$21,"*"&$G$1&"*")&B4)

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup to return the nth value.

    I believe you can do this with 1 ARRAY formula (CTRL+Shift+Enter):

    Code:
    =INDEX(Received_Process,SMALL(IF(InvList=FindValue,ROW(InvList)-ROW(INDEX(InvList,1,1))+1),2))
    where the 2 is the second occurrence to be found in the InvList and return that value from the InvList. Change the 2 accordingly.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup to return the nth value.

    Sorry, this: where the 2 is the second occurrence to be found in the InvList and return that value from the InvList. Change the 2 accordingly.

    should have been where the 2 is the second occurrence to be found in the InvList and return that value from the Received_Process. Change the 2 accordingly.

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
  •