Results 1 to 6 of 6

Thread: Multiple VLOOKUP???
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple VLOOKUP???

    Hi

    I have consecutive numbers ranging from 1 to 1000 in column A (A2:A1001). Alphanumeric part numbers in column D (D2:D1001). On a separate sheet if I enter a number between 1 and 1000 in cell A1 for example I would like cell B1 to display the associated part number from sheet 1 column D. I know I could use VLOOKUP for this but I require the next number associated with the same part to be displayed in cell A2. Example: If the typed number 300 (sheet 2!A1) looked up part number ABC123 (sheet2!B1) and number 335 was also associated with the same part then sheet 2, cell A2 would display 335 and cell B2 ABC123, and so on.

    Any help would be gratefully appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Multiple VLOOKUP???

    How about

    AB
    11ABC123
    21
    36
    41
    5
    6
    7

    Orders



    Worksheet Formulas
    CellFormula
    B1=INDEX(Sheet1!D2:D12,MATCH(A1,Sheet1!A2:A12,0))
    A2=IFERROR(INDEX(Sheet1!$A$2:$A$12,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!$A$2)+1)/(Sheet1!$D$2:$D$12=$B$1),ROWS($A$2:$A2))),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple VLOOKUP???

    Quote Originally Posted by Fluff View Post
    How about

    AB
    11ABC123
    21
    36
    41
    5
    6
    7

    Orders



    Worksheet Formulas
    CellFormula
    B1=INDEX(Sheet1!D2:D12,MATCH(A1,Sheet1!A2:A12,0))
    A2=IFERROR(INDEX(Sheet1!$A$2:$A$12,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!$A$2)+1)/(Sheet1!$D$2:$D$12=$B$1),ROWS($A$2:$A2))),"")
    Thank you Fluff. It does exactly what was required. Out of curiosity what does the AGGREGATE function do with 15,6 and also ROWS($A$2:$A9) at the end of the formula?

    Again, thanks for your help.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Multiple VLOOKUP???

    The 15,6 in the Aggregate function is the same as the Small function, but it ignores errors and the Rows unction will return 1 for the 1st row, 2 for the 2nd etc.
    Have a look here for some more info https://exceljet.net/excel-functions...egate-function
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple VLOOKUP???

    Quote Originally Posted by Fluff View Post
    The 15,6 in the Aggregate function is the same as the Small function, but it ignores errors and the Rows unction will return 1 for the 1st row, 2 for the 2nd etc.
    Have a look here for some more info https://exceljet.net/excel-functions...egate-function
    Fluff

    Thanks for the link. I've taken a look at the examples. With the 19 functions and 7 options it appears to be really useful.

    Thank you

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Multiple VLOOKUP???

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •