how do I use vlookup to get more then one line?

everything I try calls up the same line of information...

ie....

I would like to use the value of C8, and pull out all the rows above
it that have the same value in column A, and copy them to rows
11,12,13,14...


A B C D
1 WHO 123 456 789
2 WHY ABC DEF GHI
3 WHO 101 112 131
4 WHEN 123 321 321
5 WHERE 454 HGH
6 WHO OIUI IOOI OIJ
7
8 WHO
9
10
11 WHO 123 456 789
12 WHO 101 112 131
13 WHO OIUI IOOI OIJ
14


thanks for the help

Wally

Try this ..

Assume A8 contains the key value, say: WHO
(I used A8, not C8 as in post - typo?)

Use an empty col to the right, say col E?
Put in E1: =IF(A1=$A$8,ROW(),"")
Copy E1 down to E6

Put in A11:

=IF(ISERROR(SMALL($E:$E,ROWS($A$1:A1))),"",INDEX(A :A,MATCH(SMALL($E:$E,ROWS(
$A$1:A1)),$E:$E,0)))

Copy A11 across to D11, fill down to D16
(cover the same vertical range as was done in E1:E6)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WTG" wrote in message
...

how do I use vlookup to get more then one line?

everything I try calls up the same line of information...

ie....

I would like to use the value of C8, and pull out all the rows above
it that have the same value in column A, and copy them to rows
11,12,13,14...


A B C D
1 WHO 123 456 789
2 WHY ABC DEF GHI
3 WHO 101 112 131
4 WHEN 123 321 321
5 WHERE 454 HGH
6 WHO OIUI IOOI OIJ
7
8 WHO
9
10
11 WHO 123 456 789
12 WHO 101 112 131
13 WHO OIUI IOOI OIJ
14


thanks for the help

Wally

Thanks, I'll try it.


On Sat, 4 Jun 2005 22:07:17 +0800, "Max" wrote:

Try this ..

Assume A8 contains the key value, say: WHO
(I used A8, not C8 as in post - typo?)

Use an empty col to the right, say col E?
Put in E1: =IF(A1=$A$8,ROW(),"")
Copy E1 down to E6

Put in A11:

=IF(ISERROR(SMALL($E:$E,ROWS($A$1:A1))),"",INDEX( A:A,MATCH(SMALL($E:$E,ROWS(
$A$1:A1)),$E:$E,0)))

Copy A11 across to D11, fill down to D16
(cover the same vertical range as was done in E1:E6)

You're welcome !.
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WTG" wrote in message
...
Thanks, I'll try it.