Vlookup, if statement, maybe an and statement

[Jennifer]

Here is a sample of a 4000 line spreadsheet
A B C D E
Co Code Acct # Name Inv# Amt
12200 1000030 ABC 43372 30.000
12200 1000001 DEF 48895 20.000
12200 1000001 DEF 52432 20.000
12200 1000001 DEF 52254 -20.000
12200 1001185 GHI 47056 50.000
12200 1001185 GHI 53166 50.000
12200 1001185 GHI 50463 60.000
12200 1001185 GHI 47054 70.000
12200 1001185 GHI 50407 -50.000
12200 1001185 GHI 50349 -60.000
12200 1001185 GHI 47057 -70.000
12200 1001185 GHI 47055 -50.000
12200 1000178 JKL 53167 20.000
12200 1001275 LMN 52090 104.000
12200 1001275 LMN 52091 104.000
12200 1001275 LMN 52089 -200.000
12200 1001296 OPQ 52324 463.000
12200 1001296 OPQ 52325 463.000


I want to be able to identify the credits and the possible invoices that can
be offset by account number. For example, line 4 above can possibly be
matched with line 2 or 3 but not line 13 (co jkl). I've tried a number of
combinations if vlookup with IF(and) and can not seem to get it to work. If
have even created another column using =IF(E1<0,e1*-1," ") which gives me an
exact figure to match to but I can not seem to make it work. Can you help?
Thanks.

 

[Fred Smith]

So what do you want to match? We can see you want to match amount (ignoring
the sign), but what else? Obviously not the Inv#, but what about Co, Code
and Acct#? Which of those are necessary, and which not? And are your
headings correct? In the table, "Co" is a 5-number code, but in your
description "Co" is a three-character code (jkl).

Regards,
Fred