compare two groups of data

[Guest]

I am using excel 2002. I have two groups of data in one spreadsheet that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000 statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the value is
in D and return that answer. Since there are more account numbers in C than B
I can not seem to make VLOOKUP work, but that function really confuses me.

 

[Guest]

yup..
as far as i know, VLOOKUP is enough for this job.
may i know what really confuses you?

 

[Guest]

the function arguments. Sometimes if I try to answer the function values and
then do f2 I can see what it is trying to do. Can you tell me what would go
in each function argument based on the coumn info I gave?

 

[Guest]

i'll use the column E to do the vlookup
=VLOOKUP(B1,C$1$33000,2,0)
and copy the formula for all B.
explanation : find value of B1 in C1:C33000. If found, take the 2nd column
of C133000, so it must be in column D.

If you don't want to get bothered with "#N/A"s, replace the formula
=IF(ISERROR(VLOOKUP(B1,C$1$33000,2,0)),"",VLOOKUP(B1,C$1$33000,2,0))

 

[BenjieLop]

I am using excel 2002. I have two groups of data in one spreadshee
that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, Ther
are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,00
statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what th
value is
in D and return that answer. Since there are more account numbers in
than B
I can not seem to make VLOOKUP work, but that function really confuse
me.

I will do this in 3 steps ... there may be a more elegant and efficien
solution but meanwhile, this might work for you.

I will assume that all the account numbers in Columns B and C are al
unique, i.e, there are no duplications WITHIN each column. Let us sa
that the account number that you want to be looked up is entered i
Cell E1.

STEP 1: DETERMINE IF THE ENTRY IN CELL E1 IS IN B1:B400

You can enter this formula in, say, Cell F1

=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,B1:B4000))))

If the account number in Cell E1 is in B1:B4000 (and since the accoun
numbers are unique in this column as ASSUMED above), your answer in th
above formula will be 1. If the account number is not in B1:B4000, the
your answer will be a 0 (zero).

STEP 2: DETERMINE IF THE ENTRY IN CELL E1 IS IN C1:C3300

You can enter this formula in, say, Cell F2

=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,C1:C33000))))

Again, if the account number in Cell E1 is in C1:C33000 (and since th
account numbers are unique in this column as ASSUMED above), you
answer in the above formula will be 1. If the account number is not i
C1:C33000, then your answer will be a 0 (zero).


STEP 3 : ENTER THIS FORMULA IN, SAY, CELL E

=IF(and(F1=1,F2=1),VLOOKUP(E1,C133000,2,0),"N/A")

Hope this will help you.

Regards