If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup for multiple results
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??? |
#2
|
|||
|
|||
Vlookup for multiple results
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??? |
#3
|
|||
|
|||
Vlookup for multiple results
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??? |
#4
|
|||
|
|||
Vlookup for multiple results
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. |
Thread Tools | |
Display Modes | |
|
|