vendredi 3 mai 2019

How to Vlookup multiple values and return results in a column? I tried with an array formula but it results in rendering the Excel too slow

The work gets done using the below formula, however it makes the Excel too slow. Is there a similar formula I could use?

I have a long list of entries structured in the following way (Month, content, account, amount). I need to display all entries that have the same account and the occur in the same months together in a table were also content and amount are displayed. Therefore, I need that each time Month & Account are the same, the entire rows of these elements are listed in a separate table.

=IFERROR(INDEX('Account Payable 19-20'!B:B,SMALL(IF('Account Payable 19-20'!$G:$G=($C$2&$B$4),ROW('Account Payable 19-20'!B:B)-MIN(ROW('Account Payable 19-20'!B:B))+1),ROWS($M$6:N7))),"")

=IFERROR(INDEX('Account Payable 19-20'!C:C,SMALL(IF('Account Payable 19-20'!$G:$G=($C$2&$B$4),ROW('Account Payable 19-20'!C:C)-MIN(ROW('Account Payable 19-20'!C:C))+1),ROWS($M$6:N7))),"")

=IFERROR(INDEX('Account Payable 19-20'!D:D,SMALL(IF('Account Payable 19-20'!$G:$G=($C$2&$B$4),ROW('Account Payable 19-20'!D:D)-MIN(ROW('Account Payable 19-20'!D:D))+1),ROWS($M$6:N7))),"")

Aucun commentaire:

Enregistrer un commentaire