0

I'm stuck on this excel problem and have searched for a solution with no avail. Hopefully you can help!

In Column A I have a list of customers and in Column B I have an even longer list of Invoices. Basically what I am trying to do is match the most recent invoice to the customer. I tried using =INDEX(B:B, MATCH(1,IF(ISERR(FIND(A1,B:B)),0,1),0)) This formula searches for Customer 1's name in the list of invoices, and brings back the first invoice it finds to C1.

This formula works wonderfully - as long as the customer has only one invoice. After that the formula is useless because I need the most recent invoice, not the first one it finds.

What I am trying to accomplish is in the table below. Notice that Customer 4 has Invoice #002 and #003, and I need the most recent (or the one closest to the bottom) matched to that customer.

+------------------------------------------------------------------------+
| Customers      Invoices                      Recent Invoice            |
+------------------------------------------------------------------------+
| Customer 1     [Invoice #001] Customer 3     [Invoice #004] Customer 1 |
| Customer 2     [Invoice #002] Customer 4     N/A                       |
| Customer 3     [Invoice #003] Customer 4     [Invoice #001] Customer 3 |
| Customer 4     [Invoice #004] Customer 1     [Invoice #003] Customer 4 | 
+------------------------------------------------------------------------+

It seemed so simple to me... start at the bottom and search upwards... but I guess it's not so simple.

Any help would be greatly appreciated!

-J

0

This will find the last entry for each customer:

=INDEX(B:B,AGGREGATE(14,6,ROW($B$2:INDEX(B:B,MATCH("zzz",B:B)))/(ISNUMBER(SEARCH(A2,$B$2:INDEX(B:B,MATCH("zzz",B:B))))),1))

If you want the second to last change the last 1 to 2.

  • Wow this worked great! And you read my mind with the ability to search for the second to last. Thanks so much!! – Joseph Aug 11 '17 at 18:17
  • @Joseph please mark as correct by clicking the check mark by the answer. – Scott Craner Aug 11 '17 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.