Index-Match Formula is pulling incorrect value

I am trying to pull some value from 2 combined column. The problem is the value shown is always pulled from the 1st row. Any thoughts?
Thank you very much for your help.
William

This is the link to the excel sheet: http://www.filedropper.com/questiontoexcelforum

Below is the printscreen of the issue (the red-coloured-font one)
Capture.jpg

At a glance, I believe that in order to get the correct index value, you need to determine the row number where both criteria are met and return the Index value from that particular row number.

I'll have a look in more detail.

Just one more thing - many users won't be able to access screenshots due to corporate network restrictions. Heck... I couldn't access this forum until 6 months ago!

In your formula, you are asking excel to match 2 criteria and return an index value, which it does.

From your tables, you have the same criteria meeting multiple index values and excel will return the first instance and think it's done.

You have the same invoice number and product code in the same table, each of them have a different batch number, which is the value you are trying to return from your index - I can't see how this will work as your logic is:

On the same row, Match the invoice, Match the product code, return the value from 'Batch' column

however there are several values to excel could return but no logic to specify which.

What factor determines which of the batch codes should be returned?

INVOICE PRODUCT CODE QUANTITY BATCH
3210259991 BE12C6GHR 1 B112
3210259991 BE12C6GHR 1 B112
3210259991 BE12C6GHR 1 A337
3210259991 BE12C6GHR 2 Z792