Budget Array Formula lookup pulling transactions in wrong order

Hi Excel gurus

The context is, I am trying to get the top 5 transactions[amount] addresses, within two dates, with the type of expenses, using small to get the 1st,2nd,3rd..etc
I then use

Please Login or Register  to view this content.

to pull information about the transaction.

Ideally I would like it to return the address of Transactions[Amount]

Example of transactions table

Date	Duplicate Check	Description	Type	Subtype	Amount
01/01/1999	Please Login or Register to view this content.	Transaction description	Type (Eg. Expenses)	Subtype (Eg. Eating Out)	$123

The below code appears to be returning the date and then sorting, using small(), rather than returning the amount and sorting.

Please Login or Register  to view this content.

I am fairly sure that the highlighted red section is the cause, returning the row would likely target the first column in that row, which is the date in my case.

I had also tried this formula, but this was matching amount, not the check column.

Please Login or Register  to view this content.

I think I have most of the pieces, I just can't seem to put them together correctly.

Can someone please assist?

Ps. If anyone can think of a better solution to identify duplicates (bearing in mind the processed date and actual date on my transaction statements differ, as do the descriptions), I would be forever in your debt!

Hi the_grinch. Welcome to the forum.

I think we are going to need a small excel workbook uploaded. It would lend better context to this solution.

You almost always get faster solution if you do so.

If you do not know how to do this (ignore the 'paperclip' icon ... it hasn't worked for a long time)
Follow these instructions instead.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

You'll want to un merge the cells in columns E:H. They cause havoc in formulas and sorting.

Then select B24:J28 and with column J active just sort ascending. That would be the simplest way.

Hey FlameRetired,

Thanks for your prompt response!

Apologies if I misspoke, the actual sorting should be done by this formula, so that I can filter the date/type and then sort by amount.

Please Login or Register  to view this content.

Merged cells are rubbish to sort, I know that much. Which is why I am relying on the SMALL() to find the 1st, 2nd, 3rd etc biggest expense and then offset(indirect(^that cell)),0,xyz) to get the other details.

I just realized, however that the formula I keep mentioning is in a cell with white text to hide it, which I forgot to highlight. B24:B28. I'll re-up with that highlighted.

In the attached this array helper formula does all the heavy sort lifting. In B24

Formula:

Please Login or Register  to view this content.

In D24 the dates

Formula:

Please Login or Register  to view this content.

In E24 the descriptions

Formula:

Please Login or Register  to view this content.

In I24 the sub types

Formula:

Please Login or Register  to view this content.

and in J24 the amounts

Formula:

Please Login or Register  to view this content.

You are welcome. Glad to help and thank you for the feedback.

Since this is takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.