I have a large table in which I have information on a number of
employees.

I want to create a smaller table on another spreadsheet that will
summarise this info. By typing in an age, for example, I want my table
to VLOOKUP employees of that age and then in the new table give names
and other info for all employees of that age.

However, when I try to do this, VLOOKUP always gives me info for the
first employee it finds in the table, so instead of getting the
following for example:

Age Name

25 Anne
25 James
25 John
25 Tom
25 Victor

I get the following:

Age Name

25 Anne
25 Anne
25 Anne
25 Anne
25 Anne

Does anyone know what I could do about this?


--
terryc
------------------------------------------------------------------------
terryc's Profile: http://www.excelforum.com/member.php...o&userid=35796
View this thread: http://www.excelforum.com/showthread...hreadid=556044

Nel post
*terryc* ha scritto:

I have a large table in which I have information on a number of
employees.

I want to create a smaller table on another spreadsheet that will
summarise this info. By typing in an age, for example, I want my table
to VLOOKUP employees of that age and then in the new table give names
and other info for all employees of that age.

However, when I try to do this, VLOOKUP always gives me info for the
first employee it finds in the table, so instead of getting the
following for example:

Age Name

25 Anne
25 James
25 John
25 Tom
25 Victor

I get the following:

Age Name

25 Anne
25 Anne
25 Anne
25 Anne
25 Anne

Does anyone know what I could do about this?


VLOOKUP always returns the first match.

I think for your needs is most suitable a Pivot Table.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

I have tried using a Pivot Table, but it isn't really helping.

I need the user to be able to type in an age, and then the table I've
described should be automatically generated.

The data will also be automatically graphed on another worksheet.

Does anyone else have any ideas how I could get around the problem of
VLookup only returning the first value it finds?


--
terryc
------------------------------------------------------------------------
terryc's Profile: http://www.excelforum.com/member.php...o&userid=35796
View this thread: http://www.excelforum.com/showthread...hreadid=556044

Nel post
*terryc* ha scritto:

I have tried using a Pivot Table, but it isn't really helping.

I need the user to be able to type in an age, and then the table I've
described should be automatically generated.

The data will also be automatically graphed on another worksheet.

Does anyone else have any ideas how I could get around the problem of
VLookup only returning the first value it finds?

With a Pivot Table your user instead of typing an age should just (in a very
simple manner) select it from a drop down list...




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Lets say that the names are in Column A,
And the ages are in Column D,
And your data list is A1 to D100.
Say the user enters the age to lookup in E1.

Try this *array* formula:

=IF(COUNTIF(D$1:D$100,E$1)=ROWS($1:1),INDEX(A$1:A $100,SMALL(IF(D$1:D$100=E$
1,ROW($1:$100)),ROWS($1:1))),"")

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* using the CSE, copy down more rows then you anticipate a return,
just to insure all possible names are displayed.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"terryc" wrote in
message ...

I have tried using a Pivot Table, but it isn't really helping.

I need the user to be able to type in an age, and then the table I've
described should be automatically generated.

The data will also be automatically graphed on another worksheet.

Does anyone else have any ideas how I could get around the problem of
VLookup only returning the first value it finds?


--
terryc
------------------------------------------------------------------------
terryc's Profile:
http://www.excelforum.com/member.php...o&userid=35796
View this thread: http://www.excelforum.com/showthread...hreadid=556044