Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    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
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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



    ,
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It would be much easier if you could repeat the account name for each signatory.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    HowardC (2016-01-13)

  6. #5
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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.
    Attached Files Attached Files

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

    HowardC (2016-01-13)

  8. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Rory for your advice

    Kevin thanks for your updated formula & input

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •