Excel find if user paid monthly invoice

Question

I have two worksheets

Worksheet1 looks like:

Customer  | June | July | August
John      |      |      |     
Jane      |      |      |     
Bob       |      |      |     
Ann       |      |      |

Worksheet2 looks like:

Customer  | Paid Amount | Month
John      | $50         | June    
Jane      | $20         | June    
Bob       | $100        | June    
Ann       | $10         | June  

John      | $30         | July    
Jane      | $40         | July      
Ann       | $5          | July  

Bob       | $10         | August
John      | $10         | August
Jane      | $20         | August

In Worksheet1 I need a formula that simply returns a string, like "Paid" or "Not Paid", by taking the Customer name from Worksheet1, doing a lookup on Worksheet2 for that customer name, and checking if there is a payment record for that month. Based on the example data above, the result in Worksheet1 should look like:

Customer  | June | July     | August
John      | Paid | Paid     | Paid    
Jane      | Paid | Paid     | Paid    
Bob       | Paid | Not Paid | Paid   
Ann       | Paid | Paid     | Not Paid

I tried the following:

IF(ISNA(VLOOKUP([@Customer],table_from_Worksheet2,2,FALSE)),"No","Yes")

Problem is that's checking the entire Worksheet2 and not filtering by the month in question. I need to somehow tweak the VLOOKUP (or use another method such as INDEX and MATCH but I don't know how to use them) to find if customer made a payment for that specific month.

Adam Adam · Accepted Answer · 2015-08-07T23:59:57

Consider using a pivot table with "month" in the column labels box, "Name" in the row labels box, and "count of paid amount" in the values box.

Select your whole table on sheet 2.

Select Insert-->PivotTable (Far left of the ribbon)

Drag and drop the categories as I described above.

This will give you a 1 in every month/name combination where a customer has paid, and a blank otherwise. Now select the interior of your Pivot Table, Right Click and "Change Format" Select Custom Format, and scroll down to the Type called "TRUE";"TRUE";"FALSE" and apply.

Right Click on the "Grand Total" and "Remove Grand Total" if you so desire.

Pivot tables are scary at first, but very powerful and worth learning.

(I added another person before I made the table below)

Excel find if user paid monthly invoice

2 Answers