# Stuck on Index/Match formula

#### Carno

##### New Member
I'm looking for assistance with an index/match type situation where I have a transaction table containing all the unique transaction codes which I then need to append to an invoice table.

To assist with the matching, I created a unique ID in each table consisting of Account#+TransDate+TransAmt.

This works fine unless a customer makes multiple payments on the same day in the same amount. Unfortunately, this happens a lot. As you can see in the image, my formula fails in this situation because it is always returning the first match. I would like to find a way to extract and append the subsequent matches.

Here is an example of the raw invoice data as it is received (csv) before I import it into the invoice table.

With regard to the TransID's in the trans table, the number to the left of the decimal is the 'batch' number for that day's payments. The number to the right of the decimal indicates the payment's position in the batch file. If there is only one payment for the day, there will be no number to the right of the decimal. In this particular example, the four trans id's highlighted in yellow are sequential but this is not always the case. Note how Acct# 4628 has two payments on 20210226 but the trans ids are 52144.1 and 52144.12.

Any suggestions on how to handle this much appreciated! Totally open to other methods for solving it.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### Well-known Member
First Update Your Account Details to We Know what is Best option for you Based Excel & OS Version and Save it.
I don't have Correct formula at Cell E2, But I think You Can Replace it with This Formula (after Input Fromula Press CTRL+SHIFT+ENTER):
First correct source address if is misspelling. AND After Press CTRL+SHIFT+ENTER Drag it Down.
Excel Formula:
``=INDEX(tbl_Transaction18[TransID],SMALL(IF(tbl_Transaction18[UniqueID]=[@[UniqueID (Account#+TransDate+TransAmt)]],ROW(tbl_Transaction18[UniqueID])-ROW(INDEX(tbl_Transaction18[UniqueID],1,1))+1),COUNTIF(\$E\$2:E2,E2)))``

Last edited:

Replies
1
Views
194
Replies
0
Views
334
Replies
1
Views
409
Replies
1
Views
186
Replies
0
Views
222

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,982
Members
425,320
Latest member
Galin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back