| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup, Dynamic Range or Something else
I am trying to create a lookup table. I have a concatenated field on two
sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
Excel always will grab the first matching result in a field for a VLOOKUP.
Clarification: Do you need all results for "GAATSPEC" to appear in one cell? You could write an "IF" argument for each possible Concat field, but you are limited in the number of arguments in Excel 03 to 7 for nested formulas in one cell. -- Regards "Ess" wrote: I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
GAATSPEC...ALLERGY
GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
Yes, all the data is grouped together in a contiguous range.
-- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
Try this...
Sheet1 Rng1 refers to A$2A$20 Rng2 refers to B$2:B$20 Sheet2 A1 = some lookup value like GAATSPEC Enter this formula in B1. This will return the count of records for the lookup value. =COUNTIF(Rng1,A1) Enter this formula in A2. This will extract the records for the lookup value. =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) Copy down to a number of cells that is at least equal to the maximum count of any lookup value. For example, if lookup value XXX appears the most times, say 10 times, then you have to copy the formula down to at least 10 cells. -- Biff Microsoft Excel MVP "Ess" wrote in message ... Yes, all the data is grouped together in a contiguous range. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
T. Valko, it took me at least an hour to realize Rng1 and Rng2 meant range.
After the lightbulb came on, I was able to test your formulas. THEY WORK! Thank you for being the expert; it keeps up novice poeple learning. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: Try this... Sheet1 Rng1 refers to A$2A$20 Rng2 refers to B$2:B$20 Sheet2 A1 = some lookup value like GAATSPEC Enter this formula in B1. This will return the count of records for the lookup value. =COUNTIF(Rng1,A1) Enter this formula in A2. This will extract the records for the lookup value. =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) Copy down to a number of cells that is at least equal to the maximum count of any lookup value. For example, if lookup value XXX appears the most times, say 10 times, then you have to copy the formula down to at least 10 cells. -- Biff Microsoft Excel MVP "Ess" wrote in message ... Yes, all the data is grouped together in a contiguous range. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
it took me at least an hour to realize Rng1
and Rng2 meant range. Well, next time I'll try to make it more obvious. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ess" wrote in message ... T. Valko, it took me at least an hour to realize Rng1 and Rng2 meant range. After the lightbulb came on, I was able to test your formulas. THEY WORK! Thank you for being the expert; it keeps up novice poeple learning. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: Try this... Sheet1 Rng1 refers to A$2A$20 Rng2 refers to B$2:B$20 Sheet2 A1 = some lookup value like GAATSPEC Enter this formula in B1. This will return the count of records for the lookup value. =COUNTIF(Rng1,A1) Enter this formula in A2. This will extract the records for the lookup value. =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) Copy down to a number of cells that is at least equal to the maximum count of any lookup value. For example, if lookup value XXX appears the most times, say 10 times, then you have to copy the formula down to at least 10 cells. -- Biff Microsoft Excel MVP "Ess" wrote in message ... Yes, all the data is grouped together in a contiguous range. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
I think I'm doing something wrong. The formula worked when I tested in on a
blank worksheet, but when I put it in the actual workshee, I get blank fields. Here is what I typed: Rng 1 = A$6:A$10754 Rng 2 = E$6:E$10754 Lookup value = $E$5 B1 = Countif(Rng1,$E$5). My result is 20. '=IF(ROWS('Group Detail'!A$6:A$10754)B$10," ", INDEX('Group Detail'!E$6:E$10754,MATCH($E$5,'Group Detail'!$A$5:$A$10754,0)+ROWS('Group Detail'!A$6:A$10754)-1)) Please help me understand what is being reference in the formula you provided: =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) IF(ROWS(A$2:A2). Which field should I be referencing? Rng 1, Rng 2 or neither? B$1. What is the purpose of this statement? I read it as saying if the rows are greater than my count, give me zero results. Because I am using a range, the my rows will always exceed my count. ROWS(A$2:A2)-1). Am I referencing Rng1, Rng2 or something else? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: it took me at least an hour to realize Rng1 and Rng2 meant range. Well, next time I'll try to make it more obvious. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ess" wrote in message ... T. Valko, it took me at least an hour to realize Rng1 and Rng2 meant range. After the lightbulb came on, I was able to test your formulas. THEY WORK! Thank you for being the expert; it keeps up novice poeple learning. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: Try this... Sheet1 Rng1 refers to A$2A$20 Rng2 refers to B$2:B$20 Sheet2 A1 = some lookup value like GAATSPEC Enter this formula in B1. This will return the count of records for the lookup value. =COUNTIF(Rng1,A1) Enter this formula in A2. This will extract the records for the lookup value. =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) Copy down to a number of cells that is at least equal to the maximum count of any lookup value. For example, if lookup value XXX appears the most times, say 10 times, then you have to copy the formula down to at least 10 cells. -- Biff Microsoft Excel MVP "Ess" wrote in message ... Yes, all the data is grouped together in a contiguous range. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup, Dynamic Range or Something else
Disregard, I found my error and the formula is working again.
-- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: it took me at least an hour to realize Rng1 and Rng2 meant range. Well, next time I'll try to make it more obvious. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ess" wrote in message ... T. Valko, it took me at least an hour to realize Rng1 and Rng2 meant range. After the lightbulb came on, I was able to test your formulas. THEY WORK! Thank you for being the expert; it keeps up novice poeple learning. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: Try this... Sheet1 Rng1 refers to A$2A$20 Rng2 refers to B$2:B$20 Sheet2 A1 = some lookup value like GAATSPEC Enter this formula in B1. This will return the count of records for the lookup value. =COUNTIF(Rng1,A1) Enter this formula in A2. This will extract the records for the lookup value. =IF(ROWS(A$2:A2)B$1,"",INDEX(Rng2,MATCH(A$1,Rng1, 0)+ROWS(A$2:A2)-1)) Copy down to a number of cells that is at least equal to the maximum count of any lookup value. For example, if lookup value XXX appears the most times, say 10 times, then you have to copy the formula down to at least 10 cells. -- Biff Microsoft Excel MVP "Ess" wrote in message ... Yes, all the data is grouped together in a contiguous range. -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. "T. Valko" wrote: GAATSPEC...ALLERGY GAATSPEC...ASTHMA GAATSPEC...SINUS GAATSPEC...IMMUNOLOGY Is your data sorted or grouped together like above? All the GAATSPEC entries are grouped in a contiguous range? Or, is the data in random rows like this: GAATSPEC...ALLERGY XXX...yyy AAA...ooo GAATSPEC...ASTHMA -- Biff Microsoft Excel MVP "Ess" wrote in message ... I am trying to create a lookup table. I have a concatenated field on two sheets and want all the values that match that field to be populated on one of the sheets. Example: Sheet 1 (flat file) Concat field Name GAATSPEC ALLERGY GAATSPEC ASTHMA GAATSPEC SINUS GAATSPEC IMMUNOLOGY SHEET 2 (results file) Concat field Name GAATSPEC In essence, whenever I select "GAATSPEC" on the second sheet, I want it to automatically populate with the matching name. My problem is a Vlookup only gives me the first row (i.e., GAATSPEC - ALLERGY). How do I get all the other rows to populate with a name? Is this doable? -- If you never attempt anything, you will never make any mistakes...thus never enjoying the fruits of accomplishment. . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP using a dynamic range | Excel Discussion (Misc queries) | |||
| Vlookup - dynamic range reference? | Excel Discussion (Misc queries) | |||
| Dynamic range for Table_array in a VLOOKUP. | Excel Worksheet Functions | |||
| Urgent Dynamic Range with Vlookup | Excel Discussion (Misc queries) | |||
| Dynamic Range for Function (Vlookup etc) | Excel Worksheet Functions | |||
Linear Mode
