I am trying to create a formula that will compare lists and return data for
as many times as it finds the name in a column.

For instance, I have a vendor listing in column A with a review date in
column B. I want to search the vendor list and have the results return how
many times that vendor is found in the list and the date that corresponds
with the vendor.

Example
Vendor Name in column A
Office Depot
Staples
Office Max
Office Depot

Column B shows the date
02/11/05
03/5/03
04/15/06
12/14/07

I want to search the list for Office Depot for example and have it return
all the times it is listed in the column A and return the date that is in
column B for that vendor.

I tried using =INDEX and it keeps returning the wrong cell information. It
is pulling from a different cell when it returns information.

What am I doing wrong???

Hi,
Let's assume that you enter in cell c1 the vendor and you want in D1 what is
in column B

in D1 enter

=sumproduct(--(c1=a:a),b:b)

if you are not using excel 2007 enter


=sumproduct(--(c1=$a$1:$a$1000),$b$1:$b$1000) if you need to change the
range you need to have the same range in the formula in both segments


"Kris" wrote:

I am trying to create a formula that will compare lists and return data for
as many times as it finds the name in a column.

For instance, I have a vendor listing in column A with a review date in
column B. I want to search the vendor list and have the results return how
many times that vendor is found in the list and the date that corresponds
with the vendor.

Example
Vendor Name in column A
Office Depot
Staples
Office Max
Office Depot

Column B shows the date
02/11/05
03/5/03
04/15/06
12/14/07

I want to search the list for Office Depot for example and have it return
all the times it is listed in the column A and return the date that is in
column B for that vendor.

I tried using =INDEX and it keeps returning the wrong cell information. It
is pulling from a different cell when it returns information.

What am I doing wrong???

WHAT??
You lost me.

I want to type in column C the vendor I want it to search for in column A
from there you severely lost me.

I want it to search and return information for every time that vendor is
listed with a date out of column B for each reference.


"Eduardo" wrote:

Hi,
Let's assume that you enter in cell c1 the vendor and you want in D1 what is
in column B

in D1 enter

=sumproduct(--(c1=a:a),b:b)

if you are not using excel 2007 enter


=sumproduct(--(c1=$a$1:$a$1000),$b$1:$b$1000) if you need to change the
range you need to have the same range in the formula in both segments


"Kris" wrote:

I am trying to create a formula that will compare lists and return data for
as many times as it finds the name in a column.

For instance, I have a vendor listing in column A with a review date in
column B. I want to search the vendor list and have the results return how
many times that vendor is found in the list and the date that corresponds
with the vendor.

Example
Vendor Name in column A
Office Depot
Staples
Office Max
Office Depot

Column B shows the date
02/11/05
03/5/03
04/15/06
12/14/07

I want to search the list for Office Depot for example and have it return
all the times it is listed in the column A and return the date that is in
column B for that vendor.

I tried using =INDEX and it keeps returning the wrong cell information. It
is pulling from a different cell when it returns information.

What am I doing wrong???

Kris wrote...
....
For instance, I have a vendor listing in column A with a review date in
column B. *I want to search the vendor list and have the results return how
many times that vendor is found in the list and the date that corresponds
with the vendor.

Example
Vendor Name in column A
Office Depot
Staples
Office Max
Office Depot

Column B shows the date
02/11/05
03/5/03
04/15/06
12/14/07

I want to search the list for Office Depot for example and have it return
all the times it is listed in the column A and return the date that is in
column B for that vendor.
....

Simplest to select your 2-column list and apply and autofilter (menu
command Data Filter Autofilter). You may want to add a row above
the list with Vendor in col A and Date in col B of this new top row,
and include this new top row in your selected range when you apply the
autofilter.

The autofilter will add drop-down buttons to each column of the list.
You could use the col A drop-down button to select individual vendors
from the list.

If you have to use formulas, they're NOT simple formulas. I'll assume
you enter the vendor for which you want to extract records in cell F1.
If your list were in A2:B5 (you'll need to change this to correspond
to your actual list), you could use the following formulas.

F2:
=MATCH($F$1,$A$2:$A$5,0)

G2:
=IF(COUNT($F2),INDEX($A$2:$B$2,$F2,1),"")

H2:
=IF(COUNT($F2),INDEX($A$2:$B$2,$F2,2),"")

F3:
=IF(COUNT(F2),MATCH($F$1,INDEX($A$2:$A$5,F2+1):$A$ 5,0)+F2,"")

Fill G2:H2 down into G3:F3, then fill F3:H3 down as far as needed.