Search/Lookup Formula?

[asother]

I will explain my scenario:

I have two sheets of information.
In column 1 of Sheet 1 and Column 1 of Sheet 2 I have a lot of the same client information.

For example:
Sheet1 column 1:
Client A
Client B
Client C
Client D
Client E

Sheet 2 Column 1:
Client E
Client G
Client H

I want to be able to create a formula that can look up the clients in sheet 2 to see if they exist from sheet 1, if they do, then I want to make a formula to subtract the corresponding value that is in column 2 on both sheets.

I could drag the formula down the sheet to match up the others.

Thanks for any help!

[Flintstone]

Hello Asother:

I’m only guessing when you say, “formula to subtract the corresponding value that is in column 2 on both sheets”; I hope I’m helping.

Paste this formula in cell Sheet1!C2 and copy down to row 20.

=IF(COUNTIF(Sheet2!A$2:A$20,A2)>0,B2,"")

Paste this formula in cell Sheet2!C2 and copy down to row 20.

=IF(COUNTIF(Sheet1!A$2:A$20,A2)>0,B2,"")

Assuming a total is required for column B, paste this formula in cell B21 on both sheets.

=SUM(B2:B20)-SUM(C2:C20)

Hide column C in both sheets.


Matt

[asother]

Thanks for the quick response, possibly the countif is the right formula, though this doesn't quite do what I want it do. Let me see if I can make it more clear, try a sheet with this:

Sheet1:

Client Invoices Payments Total Balance

Client1 $650.00 $0.00 $650.00
Client2 $900.00 $0.00 $900.00
Client3 $700.00 $0.00 $700.00
Client4 $150.00 $0.00 $150.00

Sheet2:

Client Slip Value Percent % Hours Time Spent %
Client10* $49.50 0.02% 0.30 0.02%
Client1 $99.00 0.03% 0.60 0.04%
Client11* $130.00 0.04% 0.20 0.01%
Client12* $2,134.00 0.69% 3.60 0.22%

* = This client isnt listed on sheet 1, because we have not generated an invoice for them yet. Also, I have a few hundred rows of clients.

Basically I want Sheet1 to check Sheet2 for any of the same clients that it has, if it has the same client then it will subtract for example:
Client1 is on both sheets therefore: 650-99=551

Thanks for anyone who can help.. !!

[mudraker]

Closing thread

Same Question has now been asked in Proggramming forum with a link back to this thread.

This thread does not have a link to the programming thread & forum rules forbids asking the same question in multiple forums.