Index Match Function for Reconciliation of Date

**k2nike** · 07-03-2022, 02:12 AM

Hello.

I am trying to use an Index Match Function to reconcile date between statement of invoices received from Supplier and the invoices posted on my accounting software to make sure I have entered all the invoices on the accounting software.

As you can see from the attachment, the first sheet shows the statement of account from supplier with list of invoices due. Second sheet was exported from accounting software and shows the list of invoices and values posted on the accounting software.

I want to create a formula to match both the sheets to show whether I have recorded all the invoices on the accounting software.
Hence I made column on the first sheet to match and populate the invoice number. Whatever invoice number is posted(i.e. on the second sheet) will appear on the same row as the corresponding invoice number in the first sheet. If it does not, it means it is not there on the second sheet and its missing and not posted.

However the formula is not working for some reason. It is due to some formatting.
Kindly assist.

Please note. Any other method is helpful.

**AliGW** · 07-03-2022, 02:20 AM

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

**AliGW** · 07-03-2022, 02:28 AM

You are trying to compare a number value with a text value.

This will work WHERE THERE IS a match:

=INDEX(Ledger!$B$4:$B$112,MATCH(B4,--Ledger!$B$4:$B$112,0))

**oeldere** · 07-03-2022, 02:43 AM

Format both column to number

F1=B4*1

Ledger

C1=B4*1

After that the index formula

G4=INDEX(Ledger!$C$4:$D$112,MATCH($F4,Ledger!$C$4:$C$112,0),2)

Notice I changed the input of the blue sheet for testing.

See the attached file.

**k2nike** · 07-03-2022, 02:46 AM

Thank you @AliGW!

I am curious. May I know what you have changed considering I use the same formula. Also both the data should be same? Text or number?

**AliGW** · 07-03-2022, 02:53 AM

Our formulae are NOT the same. Look:

=INDEX(Ledger!$B$4:$B$112,MATCH(B4,Ledger!$B$4:$B$112,0))

=INDEX(Ledger!$B$4:$B$112,MATCH(B4,--Ledger!$B$4:$B$112,0))

The -- forces the lookup array to be seen as a number instead of text.

Ideally you would cleanse the data so that both tables contain EITHER text OR a real number in the related columns. Always match data types to avoid this sort of workaround.

Glad to have helped.

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**Czeslaw** · 07-03-2022, 04:21 AM

Array formula

Formula:

Please Login or Register  to view this content.

**AliGW** · 07-03-2022, 11:38 PM

Thanks for the rep.

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

**k2nike** · 07-04-2022, 01:57 AM

@oeldere --- all i had to was multiply by 1 to get the right formula. Big help

**oeldere** · 07-04-2022, 02:30 AM

Glad I could help.

If you are satisfied with the solution, please mark the question solved.

You can add reputation by clicking on the star * add reputation.

**abhaseeb** · 07-04-2022, 06:18 AM

Hi,

There is actually nothing wrong with your Function. The Invoice numbers are stored as text in one sheet and as number in the other which is why it is throwing an error. You can convert the values to numbers by multiplying them with 1.

Index Match Function for Reconciliation of Date

LinkBack

Thread Tools

Rate This Thread

Display

Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Re: Index Match Function for Reconciliation of Date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] index match function vs index match vba type mismatch

[SOLVED] Vlookup or Index and Match function - to get value next to date

[SOLVED] Index Match function when mutiple criteria meets and extract the date

Year to date to week - Index Match & Sum Ifs function?

[SOLVED] INDEX MATCH Function with most recent date

Complex function using Index, Match, Countifs, Closest Date -HELP!-

copy the data of a index match function according a date

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions