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(rel,INDEX(B2:DA100,,XMATCH("relationship",B1:DA1)),plan,INDEX(B2:DA100,,XMATCH("plan",B1:DA1)),FILTER(CHOOSE({1,2},A2:A100,plan),rel="employee"))

 

[dreid1011]

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))

 

[ExceLoki]

here's another one
---------------------

Book1
	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
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=FILTER(A2:A13,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))),B2:B13="employee")
Dynamic array formulas.

 

[Fluff]

here's another one

But why make it slow & volatile for no good reason?

 

[ExceLoki]

But why make it slow & volatile for no good reason?

offering options, not sure what might work best for the end user

 

[Fluff]

Using Indirect without good reason is not going to be "best" for anyone in their right mind.

 

[Stuepef]

How about

=LET(rel,INDEX(B2:DA100,,XMATCH("relationship",B1:DA1)),plan,INDEX(B2:DA100,,XMATCH("plan",B1:DA1)),FILTER(CHOOSE({1,2},A2:A100,plan),rel="employee"))

Could you please explain what the A2:A100 part of the formula is doing?

 

[Fluff]

It's returning the names in col A

 

[Stuepef]

It's returning the names in col A

The Employee name column could also be in a different spot in the spreadsheet and won't always be in column A like my example. Is there a way to write the formula if the name column varied as well?