Vlookup, if statement, maybe an and statement

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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.
 
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
 
Back
Top