Lookup & Match formula

[Danny]

Hi,

I have a worksheet that I send to HQ every week:

Col A = Reference Number
Col B = Amount
Col C = Name
etc.

At the end of the month HQ sends me a consolidated report for the month
(same column headings).

Please write me the formula on how to match my "Reference Number" to the
consolidated report and see if the "Amount" of the corresponding "Reference
Number" is still the same.

My weekly report usually has 200-300 rows. The consolidated report is up to
5,000 rows.

Thank you.

 

[Paul]

Hi,

I have a worksheet that I send to HQ every week:

Col A = Reference Number
Col B = Amount
Col C = Name
etc.

At the end of the month HQ sends me a consolidated report for the month
(same column headings).

Please write me the formula on how to match my "Reference Number" to the
consolidated report and see if the "Amount" of the corresponding "Reference
Number" is still the same.

Something like this ought to do the trick:
=IF(VLOOKUP(A2,Sheet2!A:C,2)=B2,"OK","DIF")
where this would be a new column in your original report and Sheet 2
is the consolidated report. VLOOKUP does the matching and finds the
corresponding amount while IF does the comparison to see if the value
is the same.

That should at least point you in the right direction.

 

[JMB]

You could use vlookup in an empty column next to your data. Assuming your
reference number is in A2 and the consolidated data is in another workbook on
a worksheet called Consolidated Data in A1:C5000, try:

=A2-vlookup(A2, '[WorkbookName]Consolidated Data'!A$1:B$5000, 2, 0)

then check for non-zero values (by filtering, formula, or just scanning
through it).

 

[Danny]

Thanks a lot. Have a nice evening!

You could use vlookup in an empty column next to your data. Assuming your
reference number is in A2 and the consolidated data is in another workbook on
a worksheet called Consolidated Data in A1:C5000, try:

=A2-vlookup(A2, '[WorkbookName]Consolidated Data'!A$1:B$5000, 2, 0)

then check for non-zero values (by filtering, formula, or just scanning
through it).

Hi,

I have a worksheet that I send to HQ every week:

Col A = Reference Number
Col B = Amount
Col C = Name
etc.

At the end of the month HQ sends me a consolidated report for the month
(same column headings).

Please write me the formula on how to match my "Reference Number" to the
consolidated report and see if the "Amount" of the corresponding "Reference
Number" is still the same.

My weekly report usually has 200-300 rows. The consolidated report is up to
5,000 rows.

Thank you.