IFERROR(INDEX(COLLECT.... Contains

Melissa Torrez

I am trying to use the below formula to collect LOB based on the Functional unit and Managing office provided. I am referencing another sheet which contains a matrix of Functional Units and Managing offices that fall under certain LOB

=IFERROR(INDEX(COLLECT({LOB}, {FU}, CONTAINS([Functional Unit (Dept ID)]@row, @cell, [Managing Office (Estab ID)]@row, @cell)),1)"No Result")

Paul Newcome

Ok. Change your reference table back to the second screenshot where you used the multi-select columns. From there your formula would look something like this...

=INDEX(COLLECT({Reference Sheet LOB Column}, {Reference Sheet FU Column}, HAS(@cell, FU@row), Reference Sheet MO Column}, HAS(@cell, MO@row)), 1)

Paul Newcome

Are you able to provide some screenshots for reference? The syntax is off, but without seeing what you are working with I am unsure of exactly what it should be.

Melissa Torrez

@Paul Newcome

In the example below, I am trying to populate LOB based on the Functional unit (100115) and managing office (300104) shown. I am referencing a matrix in another sheet that lists out the combinations

Shown below is the matrix where I am trying to pull the LOB which is 'GC all' based on the Functional unit of 100115 and managing office of 300104, but it is not a one for one relationship. For example, this LOB can be associated with 15 different func units and 16 different managing offices. I have them all listed down the column.

Alternatively, I tried the below as a reference matrix but that didn't work either.

Paul Newcome

Is there a row in your reference table where you have "100115" in the FU column and "300104" in the MO column on the same row? If not, is there a possibility that the LOB could be different for the FU and MO in your working sheet? If so, how would you determine which LOB to pull or would you want to pull both?

Melissa Torrez

@Paul Newcome There is not currently a row in the reference table that has that combination in the same row. I was trying to avoid that, but that may be the route I need to go to cover every possible combination. There is not a possibility that the LOB could be different for that combination of FU and MO in the working sheet.

Paul Newcome

Ok. So if the FU and MO will always have the same LOB, is there a reason you need to search on both, or would you be able to just search on one since you know they are going to be the same?

Melissa Torrez

In the second example screenshot you can see that those are all of the possible FU and MO that would result in the LOB, so for example FU 150111 can also be a different LOB if a different MO is involved.

For example FU 150111 can be associated any of the MO seen below

Paul Newcome

Right. But what I am asking is if the FU is always going to generate the same LOB regardless of MO, then why not just search for the LOB based on the FU?

Melissa Torrez

No, the FU could generate different LOBs

Paul Newcome

Ok. Change your reference table back to the second screenshot where you used the multi-select columns. From there your formula would look something like this...

=INDEX(COLLECT({Reference Sheet LOB Column}, {Reference Sheet FU Column}, HAS(@cell, FU@row), Reference Sheet MO Column}, HAS(@cell, MO@row)), 1)

Melissa Torrez

@Paul Newcome That worked beautifully! Thank you.

Paul Newcome

Happy to help. 👍️