Jeanne Wrote:
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.
I will do this in 3 steps ... there may be a more elegant and efficient
solution but meanwhile, this might work for you.
I will assume that all the account numbers in Columns B and C are all
unique, i.e, there are no duplications WITHIN each column. Let us say
that the account number that you want to be looked up is entered in
Cell E1.
STEP 1: DETERMINE IF THE ENTRY IN CELL E1 IS IN B1:B4000
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 account
numbers are unique in this column as ASSUMED above), your answer in the
above formula will be 1. If the account number is not in B1:B4000, then
your answer will be a 0 (zero).
STEP 2: DETERMINE IF THE ENTRY IN CELL E1 IS IN C1:C33000
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 the
account numbers are unique in this column as ASSUMED above), your
answer in the above formula will be 1. If the account number is not in
C1:C33000, then your answer will be a 0 (zero).
STEP 3 : ENTER THIS FORMULA IN, SAY, CELL E2
=IF(and(F1=1,F2=1),VLOOKUP(E1,C1:D33000,2,0),"N/A")
Hope this will help you.
Regards.
--
BenjieLop
------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread:
http://www.excelforum.com/showthread...hreadid=474792