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]

Do you have a formula suggestion that operates similar for older verisons of excel?

Nothing that spills, but try

=IFERROR(INDEX(INDEX($A$2:$DA$1000,,MATCH("Name",$A$1:$DA$1,0)),AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/(INDEX($A$2:$DA$1000,,MATCH("relationship",$A$1:$DA$1,0))="no"),ROWS(DC$2:DC2))),"")