get multiple matches from excel sumproduct as vba array()

Thanks

The question is can this also be done directly by using the results obtained from the sumproduct function without the conditions and loops?

thank you!

Trying to write a VBA function to compare bank books with an accounting report.
I know it takes about two days from the time the invoice is issued until the transaction is received in the bank account.
Therefore first I need to find the rows of dates and amounts.

The next step is to compare the descriptions that usually different between the invoices and the bank books and find the best match.

After finding the best match it is required to mark the lines of the bank and the invoices with a common code

For me this is a very complex task but it can save me a lot of time ...

thanks

Excellent solution

How to use parameters instead of addresses

I mean the parameters Rng, Mydate, Myval instead of A3: A12, A1, B1

What do you mean?

My workbook has 2 sheets

the first sheet with details from accounting and the other a report from transactions at the bank

I need to compare and associate (using a common code) every transaction from accounting to a bank transaction

What I need is a function that for each transaction in the accounting will return the line numbers of the matches in the bank books

For example for the first date in accounting 02/01/2020 and a value of 2544 I want to find in the bank books all transactions two days before and two days after this date with a value of 2543-2545 and get the line numbers of the matches in the bank books (lines 11-12)

Thanks

Upload a workbook with such 2 worksheets and the result that you want.

I did it
attached file

And your result is?

to get the line numbers in the bank sheet in this case lines 11-12 (are highlighted in light blue)

I want to use this function for further comparison of the descriptions as well and then choose at the end the match that will meet the criteria of the date (+ - 2 days) value (+ -1) and best partial description

The macro is then required to mark in a common code in accounting (say 1000) and the same code in the bank books if no match was found between accounting and the bank to mark "no match" in accounting

When I have a code like this, it will save me a lot of time ...

Thank you

See my post above.

Thank you!

Trying to understand and see how to use it ...

Do you have an idea how to make a fuzzy matching to the text as well?

I mean "Microsoft Comp" and "Microsoft" are 64% identical (len 9/len 14)

Then the function will return the most appropriate line number or even (the dream ...) the common code?

Google about

VBA Fuzzy Match

You will find some codes.

actually that's why I need the function to return the line numbers of the matches of the date and the amount
and then make Fuzzy Match of the text ...

And only after finding the best match text (or mismatch) select the best match line in both sheets and mark in common code...

You got the function to get the line number(s) already and that was your question in this thread.
If you want to do a Fuzzy match, you need to open a new thread.

thank you!!!!