Find multiple items

[foseco]

Hi

I have a budget spreadsheet and I am trying to produce a reports page. I have a named range of auditall which contains a date, an item name and a value. There are also named ranges, auditdate which contains just the dates, audititem which contains just the items and auditvalue which contains just the values. I want to report all the items that follow a bank charge with the date, item name and value.

I have used

=INDEX(audititem,MATCH("Bank Charges",audititem,0)+1)

which works for the first item only. I considered using match to find the row that item exists on and then starting a new search on the next row from that row number but I can't get match to match the entire criteria, only the item name and that returns the wrong row number as it returns another item with the same name that doesn't follow a bank charge.

I found the following on a site, adjusted for my sheet,

=IF(ROWS(Audit!B2)<=COUNTIF(audititem,"Bank Charges"),INDEX(audititem,AGGREGATE(15,3,((audititem="Bank Charges")/(audititem="Bank Charges")*ROW(audititem))-ROW(Audit!B2), ROWS(Audit!B2))),"")

but can only get it to return one item and if I copy it down, it doesn't return anything else.

I also tried

=TEXTJOIN(",",TRUE,IF(audititem="Bank Charges",OFFSET(audititem,1,0),""))

which does work but returns everything in one long string and only returns the items, not the other details and doesn't exclude duplicates where Bank Charges is followed by Bank Charges.

I've attached two images. The first is an example of the data. The second is the exported data I require. I just need it if the date is in the current year.

Thanks in advance for any advice.

Alex