IFERROR(INDEX(COLLECT.... Contains
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")
Best Answer
-
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)
Answers
-
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.
-
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.
-
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?
-
@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.
-
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?
-
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
-
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?
-
No, the FU could generate different LOBs
-
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 That worked beautifully! Thank you.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
