INDEX MATCH when column locations change

[Stuepef]

I have a spreadsheet that has a lot of company information on it and I need to obtain specific pieces of information from it. INDEX MATCH works out great if the match column remains in the same location on the spreadsheet, but many times, it is located in a different location each time. The total range of the data does not go passed column DA, but I am trying to avoid a formula that includes entire columns (ie. E:E) or entire rows to save file size.

For an outcome, I would like to make a list of employee names based on the relationship column = "Employee" and what plan they are on currently in the Plan Display Column. The two columns of Relationship and Plan Display Name can be located anywhere in the spreadsheet from Columns A through DA.

Below is a sample of the information and the outcome.

Here is my shot at the formlua IF the information stayed in the same column location:

{=INDEX($A$2:$A$13,SMALL(IF(($B$2:$B$13="Employee"),ROW($U$2:$U$13)-1),ROW(1:1)),1)}

{=INDEX($C$2:$C$13,SMALL(IF(($B$2:$B$13="Employee"),ROW($U$2:$U$13)-1),ROW(1:1)),1)|

 

[Fluff]

How about

=LET(nam,INDEX(A2:DA100,,XMATCH("Name",A1:DA1)),rel,INDEX(A2:DA100,,XMATCH("relationship",A1:DA1)),plan,INDEX(A2:DA100,,XMATCH("plan",A1:DA1)),FILTER(CHOOSE({1,2},nam,plan),rel="employee"))

 

[ExceLoki]

multiple posts solutions-v3.xlsm
	A	B	C	D	E	F
1	name	relationship	plan		name	plan
2	bobby jones	employee	plan abc		bobby jones	plan abc
3	frida jones	spouse	plan abc		stue anderson	plan def
4	george jones	child	plan abc		bridgette crawford	plan def
5	stue anderson	employee	plan def		jason mcclay	plan ghi
6	bridgette crawford	employee	plan def		robert mcilroy	plan ghi
7	elliot crawford	spouse	plan def		ernest schmidt	plan ghi
8	jason mcclay	employee	plan ghi
9	robert mcilroy	employee	plan ghi
10	ernest schmidt	employee	plan ghi
11	nicole schmidt	spouse	plan ghi
12	robert schmidt	child	plan ghi
13	lenard schmidt	child	plan ghi
INDEX MATCH when column locatio

Cell Formulas
Range	Formula
E2:E7	E2	=FILTER(INDIRECT(ADDRESS(2,MATCH(E1,A1:DA1,0))&":"&ADDRESS(13,MATCH(E1,A1:DA1,0))),INDIRECT(ADDRESS(2,MATCH("relationship",A1:DA1,0))&":"&ADDRESS(13,MATCH("relationship",A1:DA1,0)))="employee")
F2:F7	F2	=FILTER(INDIRECT(ADDRESS(2,MATCH(F1,A1:DA1,0))&":"&ADDRESS(13,MATCH(F1,A1:DA1,0))),INDIRECT(ADDRESS(2,MATCH("relationship",A1:DA1,0))&":"&ADDRESS(13,MATCH("relationship",A1:DA1,0)))="employee")
Dynamic array formulas.

 

[Stuepef]

How about

=LET(nam,INDEX(A2:DA100,,XMATCH("Name",A1:DA1)),rel,INDEX(A2:DA100,,XMATCH("relationship",A1:DA1)),plan,INDEX(A2:DA100,,XMATCH("plan",A1:DA1)),FILTER(CHOOSE({1,2},nam,plan),rel="employee"))

This is pretty slick - my concern is for end users that don't have 365. Do you have a formula suggestion that operates similar for older verisons of excel?

 

[Stuepef]

Maybe something like this to start, so long as those column headers are unique within the range A:DA.

Book1
	A	B	C	D	E	F	G	H	I
1							Name	Relationship	Plan
2							Bobby Jones	Employee	Plan ABC
3							Frida Jones	Spouse	Plan ABC
4							George Jones	Child	Plan ABC
5							Stue Anderson	Employee	Plan DEF
6							Bridgette Crawford	Employee	Plan DEF
7							Elliot Crawford	Spouse	Plan DEF
8							Jason McClay	Employee	Plan GHI
9							Robert McIlroy	Employee	Plan GHI
10							Ernest Schmidt	Employee	Plan GHI
11							Nicole Schmidt	Spouse	Plan GHI
12							Robert Schmidt	Child	Plan GHI
13							Lenard Schmidt	Child	Plan GHI
14
15	Outcome
16	Name	Plan
17	Bobby Jones	Plan ABC
18	Stue Anderson	Plan DEF
19	Bridgette Crawford	Plan DEF
20	Jason McClay	Plan GHI
21	Robert McIlroy	Plan GHI
22	Ernest Schmidt	Plan GHI
23
Sheet3

Cell Formulas
Range	Formula
B17:B22	B17	=INDEX(CHOOSECOLS($A$1:$DA$13,MATCH("Plan",$A$1:$DA$1,0)),MATCH(A17&"Employee",CHOOSECOLS($A$1:$DA$13,MATCH("Name",$A$1:$DA$1,0))&CHOOSECOLS($A$1:$DA$13,MATCH("Relationship",$A$1:$DA$1,0)),0))

This is interesting - is there a way to make a formula for the names using the CHOOSECOLS logic?

 

[dreid1011]

This is interesting - is there a way to make a formula for the names using the CHOOSECOLS logic?

What do you mean? My formula assumes your output is static and the input columns vary within the range A:DA. Is your output table moving too?

 

[Stuepef]

What do you mean? My formula assumes your output is static and the input columns vary within the range A:DA. Is your output table moving too?

Your formula uses A17 to reference a name. My question is is there a formula to populate the names column in range A17:A22? The INDEX would be Name and the match would be "employee" within the relationship column.

 

[dreid1011]

Your formula uses A17 to reference a name. My question is is there a formula to populate the names column in range A17:A22? The INDEX would be Name and the match would be "employee" within the relationship column.

Yeah, give this a try (I'm using 2013 at home so I can't test this):

=FILTER(CHOOSECOLS($A$1:$DA$13,MATCH("Name",$A$1:$DA$1,0)),CHOOSECOLS($A$1:$DA$13,MATCH("Relationship",$A$1:$DA$1,0))="Employee")

 

[Stuepef]

Yeah, give this a try (I'm using 2013 at home so I can't test this):

=FILTER(CHOOSECOLS($A$1:$DA$13,MATCH("Name",$A$1:$DA$1,0)),CHOOSECOLS($A$1:$DA$13,MATCH("Relationship",$A$1:$DA$1,0))="Employee")

Here is the result:

 

[dreid1011]

Here is the result:
View attachment 99770

Try it in just A17, don't drag it down.

 

[Fluff]

is there a way to make a formula for the names using the CHOOSECOLS logic?

Not with xl2021 as per your profile as it's only in xl365.