Quote Originally Posted by Jeanne
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.