Vlookup, if statement, maybe an and statement
 

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.

Vlookup, if statement, maybe an and statement
 

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

"Jennifer" wrote in message
...
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.