Need help with filtering formula.

[Clifford86]

Hello,

I am needing help with a formula for filtering based on a table row and cell.

My goal is to have an array table that will populate a drop-down list for the Sample_Reg Table.

So in the Sample_Reg Table, I have many columns.
Name--------Account Type-----Number-----Date--------------AMT 1------------AMT 2------------etc..
Filter List-----Filter List-----------Filter List-----Manual Entry-----Manual Entry-----Manual Entry-----Manual Entry


I have an array-table1 setup that works correctly to get the list of names from a different table. Then the Sample_Reg Table Name (A2) has a drop-down list of names from the array-table1.

Now I am wanting array-table2 to filter account type based on the Name selected on the Sample_Reg Table "A2".
Then the Sample_Reg Table will have a drop-down list for Account Type "B2" based on the array-table2.

Please Login or Register  to view this content.

This is working in general.

Now I am wanting Row 3 of the Sample_Reg Table to do the same thing above "HOWEVER" look in the A3 cell for the Name to filter by.
Now I am wanting Row 4 of the Sample_Reg Table to do the same thing above "HOWEVER" look in the A4 cell for the Name to filter by.
Now I am wanting Row 5 of the Sample_Reg Table to do the same thing above "HOWEVER" look in the A5 cell for the Name to filter by.
and etc...

Now I know that I can do this by creating many filter array tables and have them look in the correct cell to filter by the name selected. However, that will be a lot of work, time and whatnot. So is there a better formula or way to tell the array table to look at the correct cell on the Sample_Reg table automatically?

---
Thanks

[Limor_OP]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[Clifford86]

Hello,

I am attaching a sample with details if anyone is able to help. Thanks

[Clifford86]

Good day everyone. Just checking in to see if anyone is able to help or point me in the right direction. Thanks

[JeteMc]

I feel that it will be easier to assign the account number to the drop down based on bank name and have a formula that populates the account type.
I don't have the 365 version, so these formulas are based on functions supported by the 2019 version.
For the drop downs in the account number column: =OFFSET('Bank Info'!A$3,MATCH(A4,'Bank Info'!A$4:A$10,0),2,COUNTIFS('Bank Info'!A$4:A$10,A4))
The account type column is populated using: =INDEX('Bank Info'!B$4:B$10,MATCH(C4,'Bank Info'!C$4:C$10,0))
The account description column is populated using: =INDEX('Bank Info'!D$4:D$10,MATCH(C4,'Bank Info'!C$4:C$10,0))
Let us know if you have any questions.