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

Joecam

New member
Joined
May 22, 2014
Messages
34
Reaction score
0
Points
0
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?
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
How are these things related?

Attach a small sample workbook illustrating what you want to achieve.
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
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

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
By the way. xlsm is NOT a version of Excel! This should be something like 2019 or 365 - please update your forum profile. Thanks.
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
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?
 

Joecam

New member
Joined
May 22, 2014
Messages
34
Reaction score
0
Points
0
Excel Version(s)
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 a moderator:

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
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.
 

Joecam

New member
Joined
May 22, 2014
Messages
34
Reaction score
0
Points
0
Excel Version(s)
365
Yep, that did it! Thank you so much, it works amazing!
 
Last edited by a moderator:

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
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))),"")
 
Top