Formula to Display Values Based on 2 Criteria

[YuriyBaron]

Hi,

I have attached a sample spreadsheet to better explain what needs to happen.
In the spreadsheet, I have a table that is generated by SQL. Form this table I need to pull out certain sections. The Sections that I need to pull out are in the table next to it in yellow.
I need to type in base (in this case W128636; This will populate the table) and SubNo (in this case 5), the result needs to be a list of rsc.

I have tried the following array: {=IFERROR(INDEX($E$2:$E$41,MATCH(0,IF($H3=$C2:$C41,COUNTIF($H3:H3,$E$2:$E$41),""),0)),"")}
This array work except it removes all duplicates and I need it to display everything preferably in a column (highlighted in yellow) instead of a row.

(BASE WILL NEVER CHANGE, ONLY subNo)

Thank you

[Vraag en antwoord]

With a pivotable?
Test(55).xlsx

[YuriyBaron]

How will a pivot table know the SubNo? PivotTables cannot be modified with variable inputs. PivotTables (to my knowledge) only work with preset data, not variable data.

In this example, let's say that the SubNo changed from 5 to 7 in cell H2. How will the information in the pivot table update to represent the new variable?

[bebo021999]

Try this in I3:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Drag down.

[YuriyBaron]

I tried this and while everything worked, it did get rid of all duplicates. Please review this in the newly attached spreadsheet.

[bebo021999]

OK I see the situation. Try this instead:

Please Login or Register  to view this content.

CSE

[YuriyBaron]

Worked Perfectly! Thank you for your assistance!

Reputation added and thread closed!

[sandy666]

(BASE WILL NEVER CHANGE, ONLY subNo)

Done with PowerQuery

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel