How to make Vlookup loop through a column to find the value of cell below it and carry on

[Fuzzyfazzy]

Hi All,

I have a strange problem where I would like to search for a value, such as "K" in a Column and return the value of the cell below it.

i.e.

Column would contain the following data:

A
K
T
J
E
K
A

So in another column, the values returned would be T and A and so on...

Hoping someone can help as I have tried the INDEX method but it only seems to find the first "K" and I cannot seem to get it to loop on and get the next value.

Thanks in advance.

 

[Scott T]

Excel 2010
	A	B	C
1			what to search for
2	A		k
3	K
4	T		next row
5	J		T
6	E		A
7	K
8	A
Sheet4

Cell Formulas
Range	Formula
C5	{=IF(ROWS($C$5:C5)>COUNTIF($A$2:$A$8,$C$2),"",INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8=$C$2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($C$5:C5))+1))}
C6	{=IF(ROWS($C$5:C6)>COUNTIF($A$2:$A$8,$C$2),"",INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8=$C$2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($C$5:C6))+1))}
C7	{=IF(ROWS($C$5:C7)>COUNTIF($A$2:$A$8,$C$2),"",INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8=$C$2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($C$5:C7))+1))}
C8	{=IF(ROWS($C$5:C8)>COUNTIF($A$2:$A$8,$C$2),"",INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8=$C$2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($C$5:C8))+1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fuzzyfazzy]

Oh wow! thankyou! worked perfectly.

Excellent reply.