I have a spreadsheet that I made a sheet that shows the top transactions by dollar value.
To do that I put numbers in the first column (BA) and used the LARGE formula referencing the first column to get the nth highest amount ie
Column BA
=LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ6)
=LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ7)
=LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ8)
To get the name and the date of the transaction I was trying and INDEX LOOKUP function ie
Column BB
=INDEX(Transactions!$D:$J,MATCH($BA6,Transactions!$J:$J,0),1)
=INDEX(Transactions!$D:$J,MATCH($BA7,Transactions!$J:$J,0),1)
=INDEX(Transactions!$D:$J,MATCH($BA8,Transactions!$J:$J,0),1)
Column BC (basically the same but referencing the 2nd column after $D)
=INDEX(Transactions!$D:$J,MATCH(BA6,Transactions!$J:$J,0),2)
=INDEX(Transactions!$D:$J,MATCH(BA7,Transactions!$J:$J,0),2)
=INDEX(Transactions!$D:$J,MATCH(BA8,Transactions!$J:$J,0),2)
This only finds the first instance where the amounts in column $BA are the same, see picture below.
How can I adjust it so I find the cell that is offset from the nth occurrence instead. In the actual datasheet the name is in column D and the date in column E.
EX1.png
Thanks
Bookmarks