Thanks for your quick reply,

The code works perfectly except the fact that I wanted to populate the cells
in sheet2 without spaces, one "a" after another (consecutively).

I'll have to think how to modify this macro.

Thanks


"Jacob Skaria" wrote:

If you would like to use a formula you could try the below in Sheet2 and copy
down as required. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

=IF(COUNTIF(Sheet1!$A$1:$A$1000,"a")<ROW(A1),"",
INDEX(Sheet1!C$1:C$1000,SMALL(IF(Sheet1!$A$1:$A$10 00="A",
ROW($A$1:$A$1000)),ROW())))

--
Jacob


"lilianld" wrote:

Hi,

It's my first post and I am a novice in VBA. I know my issue is simple one
but I cannot come out with the solution. Maybe some one can help.

What I am trying to accomplish is to search trough an entire column in the
first sheet1 (A:A) for a specific value (let say "a") and return the cell
value from column "C" of the same row in sheet2.

Ex:

Sheet1 Sheet2 (the same workbook)

A B C A B C

a 2 4 4
1 5 6
8 7
a 9 6

So in this case the result of the search is shown in Sheet2


I know that I can use the vlookup function but I need to look for more than
one value in the column and return the cell values from each row.

Thanks in advance