loop backwards for searching a string

[abhimaan]

Hi,

I want to run the loop backwards in order to search a string. The requirement is as below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD][/TD]
[TD]game[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]ZOK[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD][/TD]
[TD]pen[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD][/TD]
[TD]mobile[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]KUY[/TD]
[TD]copy[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD][/TD]
[TD]shirt[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD][/TD]
[TD]rat[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD][/TD]
[TD]mat[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD][/TD]
[TD]hat[/TD]
[/TR]
</tbody>[/TABLE]


I want to copy the rows from one sheet to another based on the string I am searching.
Lets say if the string I am searching from example above is "hat" in column C and there is no value corresponding to it in cell B9, then a loop should start to search for value in column B starting from B9 till the cell where it finds some value which in this case would be in cell B5 and value is "KUY".

I want to have the code for looping in reverse order.

Any help would be much appreciated.

Thanks,
Abhimanyu

 

[mikerickson]

With that data set,
=LOOKUP("zzz", INDEX(B:B,1,1):INDEX(B:B, MATCH("hat", C:C, 0), 1))

will return KUY