Forum Discussion

sksk2424's avatar
sksk2424
Copper Contributor
Nov 17, 2022
Solved

Vlookup / Index array issues

Hello everyone,

 

I am trying to solve the following problem:

 

I have an overview table in which projects are listed in a column. Next to each project is the project status.

Now, each project has possible interdepencies, recorded in an additional column.

The interdepencies are recorded like this: "Projects 1, Project 2, Project 5".

 

I would like to search for the project name within the overview, find which interdepencies are filed and return each interdepency with its status.

 

Sadly, I can't enhance sample data or screenshots...

Thanks in advance!!

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 17, 2022

    sksk2424 

    =IFERROR(INDEX($B$2:F15,SMALL(IF($B$2:$B$15=$A$17,ROW($B$2:$B$15)-1),ROW($A1)),COLUMN(A$1)),"")

    An alternative could be INDEX and SMALL. The formula is entered in cell B17 and dragged across range B17:F20. Perhaps you have to enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

Resources