Pivot or Formula for Reverse Lookup

1

I am creating a list of features and the audiences that will be targeted for those features. Each stakeholder group (audience) is separate by a comma in the list. Now, if I want to create a pivot or some formula to see which features are applicable for a particular stakeholder group, then how would I do that.

sample table

In the image above, the table has the features listed vertically, and each feature has a list of SGs (stakeholder groups) assigned to it. Now, I want another table in where I can select any particular SG, and it will return the features this SG is mapped against. Here is the expected outcome:

Sample output

Is there a way to do this?

JayD

Posted 2019-04-08T20:38:02.907

Reputation: 11

Since SGs are duplicate therefor will return multiple Features, this is what you are suppose to do or anything else,, better edit the post and attach expected output also !! – Rajesh S – 2019-04-09T05:08:58.167

@RajeshS I added sample output – JayD – 2019-04-09T16:08:11.787

If you have a reasonable number of features and SGs, and you don't NEED the output of the vertical feature list to roll up without interstitial blanks, =IF(ISERROR(FIND(D$1,$B2)),"",$A2) copied from the first cell under SG1 across the entire sample output table will work (assuming SG1 is in D1) – Alex M – 2019-04-09T18:34:47.863

@JayD,, now check the post I've solved the issue!! – Rajesh S – 2019-04-11T05:55:51.940

Answers

0

Your problem can be solved by using few Helper Columns:

enter image description here

How it works:

Enter header SG1, SG2, SG3 & SG4 in Cell D95, E95, F95 & G95 also in I95, J95, K95 & in L95.

  • Enter this Formula in Cell D96 and fill it Down then Right.

    =IF(ISNUMBER(SEARCH(D$95, $C96)), D$95, "")

  • You get Helper Values in Range D96:G102.

  • Enter this Array (CSE) Formula in Cell I96, finish with Ctrl+Shift+Enter, fill it Down then Right.

    {=IFERROR(INDEX($A$96:$A$102,SMALL(IF(D$96:D$102=I$95,ROW($A$96:$A$102)-MIN(ROW($A$96:$A$102))+1),ROWS($I$95:I95))),"")}

  • You get expected output in Range I96:L102.

Adjust Cell references in the formula as needed.

Rajesh S

Posted 2019-04-08T20:38:02.907

Reputation: 6 800

Asked: 2019-04-08T20:38:02.907

Viewed: 60 times

Active: 2019-04-10T07:55:20.477