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

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
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?
 
How are these things related?

Attach a small sample workbook illustrating what you want to achieve.
 
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),"")
 

Attachments

  • Conditionl Fill Sample Workbook AliGW.xlsx
    14.3 KB · Views: 5
By the way. xlsm is NOT a version of Excel! This should be something like 2019 or 365 - please update your forum profile. Thanks.
 
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?
 
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 a moderator:
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.
 
Yep, that did it! Thank you so much, it works amazing!
 
Last edited by a moderator:
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))),"")
 
Back
Top