INDEX MATCH when column locations change

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
128
Office Version
  1. 2021
Platform
  1. Windows
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:

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

1696444010541.png
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
Excel Formula:
=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"))
 
Upvote 0
Maybe something like this to start, so long as those column headers are unique within the range A:DA.

Book1
ABCDEFGHI
1NameRelationshipPlan
2Bobby JonesEmployeePlan ABC
3Frida JonesSpousePlan ABC
4George JonesChildPlan ABC
5Stue AndersonEmployeePlan DEF
6Bridgette CrawfordEmployeePlan DEF
7Elliot CrawfordSpousePlan DEF
8Jason McClayEmployeePlan GHI
9Robert McIlroyEmployeePlan GHI
10Ernest SchmidtEmployeePlan GHI
11Nicole SchmidtSpousePlan GHI
12Robert SchmidtChildPlan GHI
13Lenard SchmidtChildPlan GHI
14
15Outcome
16NamePlan
17Bobby JonesPlan ABC
18Stue AndersonPlan DEF
19Bridgette CrawfordPlan DEF
20Jason McClayPlan GHI
21Robert McIlroyPlan GHI
22Ernest SchmidtPlan GHI
23
Sheet3
Cell Formulas
RangeFormula
B17:B22B17=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))
 
Upvote 0
here's another one
---------------------
Book1
ABCDEF
1namerelationshipplannameplan
2bobby jonesemployeeplan abcbobby jonesplan abc
3frida jonesspouseplan abcstue andersonplan def
4george joneschildplan abcbridgette crawfordplan def
5stue andersonemployeeplan defjason mcclayplan ghi
6bridgette crawfordemployeeplan defrobert mcilroyplan ghi
7elliot crawfordspouseplan defernest schmidtplan ghi
8jason mcclayemployeeplan ghi
9robert mcilroyemployeeplan ghi
10ernest schmidtemployeeplan ghi
11nicole schmidtspouseplan ghi
12robert schmidtchildplan ghi
13lenard schmidtchildplan ghi
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=FILTER(A2:A13,INDIRECT(ADDRESS(2,MATCH("relationship",A1:DA1,0))&":"&ADDRESS(13,MATCH("relationship",A1:DA1,0)))="employee")
F2:F7F2=FILTER(INDIRECT(ADDRESS(2,MATCH(F1,A1:DA1,0))&":"&ADDRESS(13,MATCH(F1,A1:DA1,0))),B2:B13="employee")
Dynamic array formulas.
 
Upvote 0
Using Indirect without good reason is not going to be "best" for anyone in their right mind.
 
Upvote 0
How about
Excel Formula:
=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?
 
Upvote 0
It's returning the names in col A
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top