Results 1 to 10 of 10

Thread: Conditionally filling a table based on non-unique information from another table

  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365

    Conditionally filling a table based on non-unique information from another table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I have a table located on one worksheet that contains some columns & rows that I want to pull into a second table.

    So for example, from my table called FY22_Q1 column [Classification] any time the word "Production" comes up, I want to pull the information in the [Name] column onto my new table.

    Can anyone help?

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    How are these things related?

    Attach a small sample workbook illustrating what you want to achieve.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365
    Conditionl Fill Sample Workbook.xlsx

    Sample attached. Thank you!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    In G4:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$13)/($C$1:$C$13="Production"),ROWS($1:1))),"")

    In H4:

    =IFNA(VLOOKUP([@Name],SampleTable1[[Name]:[ID]],2,0),"")

    In I4:

    =IFNA(VLOOKUP([@Name],SampleTable1[[Name]:[Position]],5,0),"")
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    By the way. xlsm is NOT a version of Excel! This should be something like 2019 or 365 - please update your forum profile. Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    And there he was, gone, with not one word of thanks for the solution offered. What happened to good manners and common courtesy, I wonder?
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365
    That worked perfectly! Thank you!

    What if column C had more options to choose from and I wanted to allow for multiple variables to be looked up, not just "Production"?

    I tried changing the formula in G4 to include an OR, but that doesn't seem to work no matter where I'm sticking the OR.
    Last edited by AliGW; 2021-04-11 at 03:36 PM. Reason: PLEASE don't quote unnecessarily!

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    Maybe this?

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$13)/(($C$1:$C$13="Production")+($C$1:$C$13="Something Else")),ROWS($1:1))),"")

    Untested.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365
    Yep, that did it! Thank you so much, it works amazing!
    Last edited by AliGW; 2021-04-11 at 03:36 PM. Reason: PLEASE don't quote unnecessarily!

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    Just for completeness, this would be an AND lookup:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$13)/(($C$1:$C$13="Production")*($D$1:$D$13="Forward")),ROWS($1:1))),"")
    Ali
    Enthusiastic self-taught user of MS Excel!

Tags for this Thread

Posting Permissions

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