# Thread: Index and Match Problem

1. ## Index and Match Problem

I have set a spreadsheet where a user can select a bank account and when doing so I would like the cheques signatories to display pertaining to the selected bank account. I have tried to use Index/match , but I get #N/A

I have attached a sample spreadsheet.

It would be appreciated if someone could kindly assist me

2. Hi HowardC

Try the following in K2 & copy down.

=IFERROR(INDEX(\$C\$2:\$C\$9,MATCH(\$J\$2,\$B\$2:\$B\$9,0)+R OW()-2),"")

3. Hi Kevin

Thanks for the help. When I select Bayler-Current Account, the formula returns the signatories applicable for that account. However, If I select Akeneler-Current Account, all the signatories are shown for both accounts. Kindly amend your formula to only display the signatories for the account selected in J2

See sample data attached

,

4. It would be much easier if you could repeat the account name for each signatory.

5. ## The Following User Says Thank You to rory For This Useful Post:

HowardC (2016-01-13)

6. Hi HowardC

I posted a bit to quick, try the following in K1 and copy down. Its a bit long winded! and is an array formula.

=IFERROR(INDEX(INDEX(\$C\$2:\$C\$9,MATCH(\$J\$2,\$B\$2:\$B\$ 9,0)):INDEX(\$C\$2:\$C\$9,MATCH(1,(\$B\$2:\$B\$9<>\$J\$2)*(\$ B\$2:\$B\$9<>"")*(ROW(\$E\$2:\$E\$9)-1),1)),ROW()-1),"")

Alternatively you could use a pivot table. If you follow rory advice.

7. ## The Following User Says Thank You to Kevin@Radstock For This Useful Post:

HowardC (2016-01-13)