VLookup, Dynamic Range or Something else

[Ess]

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?

 

[Brown Recluse]

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.

 

[T. Valko]

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

 

[Ess]

Yes, all the data is grouped together in a contiguous range.

 

[T. Valko]

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.

 

[Ess]

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.

 

[T. Valko]

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!

 

[Ess]

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?

 

[Ess]

Disregard, I found my error and the formula is working again.