Excel formula help - look up reference but also check value

Hi,

Trying to work out what formula I need for this problem, not sure if it might be something like Index and Match.
It needs to first of all check for a matching reference, but then also a matching value

Our documents are alphanumeric but the customers system only enters them their end as numbers.
When we receive their remittance it only quotes the numbers from the documents and also the values.

However, with the letters removed it means some document references are no longer unique & there is the occasional duplicated document number.

Example below
Columns A and B are our document numbers and their values.
I've then added column C and D as a starting point and have used a formula to remove the letters from our original document numbers in column A.
You'll notice that some documents in column C now appear as duplicates.

In column F and G I've then added the customers remittance.


I need a formula to show which lines on our statement are included in the remittance


(remittance and statement can be hundreds of lines)

RemitFormulaPIC.jpg

It is unclear from your example what you expect from the outcome of the formula.

Try something like

=IFERROR(LOOKUP(2,1/($C$2:$C$2000=F2)/($D$2:$D$2000=G2),$A$2:$A$2000),"No Match")

If you need more than 2000 rows, adjust the ranges as needed, making sure that they all start and end at the same rows. Don't go way beyond what is necessary, big ranges will take a long time to calculate.