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.


.



.



.