Match - Invoice and Reference

[Thomas Kuriakose]

Respected Sirs,

Kindly find attached a workbook which has three tabs -

AD - This has Invoice numbers, Reference numbers and value. There are duplicate invoice numbers for some references.
DP - This has Invoice numbers, Reference numbers and value. There are duplicate invoice numbers for some references.

Summary tab - I have manually entered the desired output with reference to tab AD and DP.

There are instances of AD tab having multiple invoices with individual references numbers. The summary tab has to match the respective invoice numbers from AD and DP and find the corresponding references and values.

kindly let me know how this can be done with formulas.

Thank you very much,

with regards,
thomas

 

[Hui]

On the Summary worksheet

D2: =INDEX(Table2[Value],MATCH([AD Invoice],Table2[Invoice],0),) Ctrl+Shift+Enter

E2: =INDEX(Table2[Reference],MATCH([AD Invoice],Table2[Invoice],0),)
Ctrl+Shift+Enter

F2: =INDEX(Table2[Invoice],MATCH([AD Invoice],Table2[Invoice],0),)
Ctrl+Shift+Enter

Copy D2:F2 down

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the support on this.

Sir, it is copying the same values for all Invoices. The desired result should be as in column I to K.

Kindly check the attached,

Thank you very much,

with regards,
thomas

 

[PCosta87]

Hi Thomas,

Like this? (see attached... columns H:J)

 

[Thomas Kuriakose]

Respected Sir,

Yes Sir, this is perfect.

Thank you so much for the solution provided.

Much appreciated,

with regards,
thomas

 

[PCosta87]

Respected Sir,

Yes Sir, this is perfect.

Thank you so much for the solution provided.

Much appreciated,

with regards,
thomas

Don't mention it

 

[bosco_yip]

Pcosta have solved your problem, herewith just for your information.

Please noted that,

1] Your summary "invoice items" set up is based on AD are more than or equal to DP,

however, if DP items are more than AD, your output table will lost some items from DP.

2] I herein enclosed a file with some adjustment, with 2 items AD more than DP and 2 items DP more than AD.

3] See attachment

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this insight and adjustment.

Thank you very much once again,

with regards,
thomas