I have these data in Sheet 1

Cell
A B C
3 112 John
3 159 Isaac
3 119 Paul
4 117 Joseph
4 114 Moses

In my Sheet 2, I will have an allocated cell for my data entry, example I
enter the number 3
When number 3 is entered, I want Row 5 to 6 in column A of Sheet 2 to
extract Sheet 1 column A, B and C to appear all data of column A,B,C in Sheet
1.

I tried lookup formula, but it is giving me the last information only that
is if I entered 3, it will give me 3 119 Paul but I want to list down from
John to Paul.

Thank you!

Jaf,

If your 'allocated cell' is cell A1 (Change the $A$1 in the COUNTIFs to the actual cell address),
then array enter this formula (enter using Ctrl-Shift-Enter)

=IF(COUNTIF(Sheet1!$A:$A,$A$1)=ROWS($A$1:A1),INDE X(Sheete1!A:A,LARGE((Sheet1!$A$1:$A$999=$A$1)*ROW( Sheet1!$A$1:$A$999),COUNTIF(Sheet1!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

and copy down and to the right.


HTH,
Bernie
MS Excel MVP


"Jaf" wrote in message
...
I have these data in Sheet 1

Cell
A B C
3 112 John
3 159 Isaac
3 119 Paul
4 117 Joseph
4 114 Moses

In my Sheet 2, I will have an allocated cell for my data entry, example I
enter the number 3
When number 3 is entered, I want Row 5 to 6 in column A of Sheet 2 to
extract Sheet 1 column A, B and C to appear all data of column A,B,C in Sheet
1.

I tried lookup formula, but it is giving me the last information only that
is if I entered 3, it will give me 3 119 Paul but I want to list down from
John to Paul.

Thank you!

Hi,

You may try this

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jaf" wrote in message
...
I have these data in Sheet 1

Cell
A B C
3 112 John
3 159 Isaac
3 119 Paul
4 117 Joseph
4 114 Moses

In my Sheet 2, I will have an allocated cell for my data entry, example I
enter the number 3
When number 3 is entered, I want Row 5 to 6 in column A of Sheet 2 to
extract Sheet 1 column A, B and C to appear all data of column A,B,C in
Sheet
1.

I tried lookup formula, but it is giving me the last information only that
is if I entered 3, it will give me 3 119 Paul but I want to list down from
John to Paul.

Thank you!