Excel help

[beststepforward]

My mind is completely blank. Which function would I use?

I’ve got two sheets. Sheet 1 has a list of client and amount due. Sheet 2 has list of bank incomings - so the description and amount which has been paid in

I need to cross reference to see which clients have paid

Would you not be better with one sheet? Sorry if am misunderstanding.

Vlookup maybe?

Does the second sheet have total payments or is it possible that there have been multiple payments made by a particular client?

is there a client id column on both sheets?

Vlookup probably
You would need something to lookup though that is common on both sheets, eg invoice number perhaps?

If you VLOOKUP the amount from the client due side in the bank payments and ask it to return date or something, then all the clients who have a matching amount will have a date next to them.

That will only work if all amounts are unique to a customer

Do both lists have a common field so is email address on both lists?

If so then you need vlookup.

Make sure your common column is column A on each sheet, otherwise it sometimes messes up, then use the function wizard to build the formula.

Vlookup requires a unique identifier. Most functions will, though sometimes you can build one with multiple columns.

if there are multiple payments on sheet 2, vlookup won’t give you the correct answer.

you have 2 options at that point. If this is a 1 time task, you can use a pivot table to sum up the amounts and then do a vlookup off the pivot table. I don’t like using pivots for repetitive tasks because they don’t automatically refresh on the data range when new records are added.

if it is a repetitive task, I suggest using SUMIF. It’s a little trickier to set up, but it is computationally quick and it will expand easily when new records are added.

If the bank reference is identifiable for each customer use a sumif. Or if each payment is individual you could use an isna(match(
If it’s multiple payments use a pivot then do an index match. ( similar to vlookup)

You will need a unique identifier for each client that is on both sheets. You can then use a SUMIF for the payments in sheet 2 to add up multiple payments by each client using the unique identifier and then vlookup this value to sheet 1. There might be other excel whizzes that can think of a more streamlined version of this.

Where others have suggested vlookup, the new xlookup is easier to use. But you do need a unique identifier between both sheets.

Xlookup is much easier than vlookup if you’ve got a newer version of excel.

Ah cross post.

Thanks all

Don’t forget you can always ask ChatGPT and it will come back with good advice!