vlookup/ if ... maybe??

[CDNGRL]

Not sure if I'm describing this properly but ....

Is there a formula that can look into page 2 of a spreadsheet and then display in a list on page 1 everything in 1st column that has the word "apple" beside it??

Please help if you can .. or point me in the proper direction ... I tried a vlookup up it keeps returning the same name over and over. I don't know how to get it to go to the next name on the list.

 

[Akashwani]

Hi,
First off I would like to say that I am not an expert and that there will be better advice than this available.
I recently had a similar situation and Dominic was kind enough to solve my problem, have a look at this link it may help you......

http://www.mrexcel.com/forum/showthread.php?t=426236&goto=newpost

Right then, if I have this right follow these steps...

For this example I am assuming that your range on sheet2 is A2:B8
Sheet1 A2 type in Apple.
Sheet1 B2 put in this... =COUNTIF(Sheet2!B2:B8,A2)
Sheet1 C2 put in this formula....

=IF(ROWS(C$2:C2)<=$B$2,INDEX(Sheet2![B]A$2:A$8[/B],SMALL(IF(Sheet2![B]$B$2:$B$8[/B]=$A$2,ROW(Sheet2![B]$A$2:$A$8[/B])-ROW(Sheet2![B]$A$2[/B])+1),ROWS(C$2:C2))),"")

Use contol-shift and enter when you have copied the formula to C2.
You can then copy down to C8.
Change your range to suit your requirements.

I hope this works and that I have given you the correct information.

Good luck.

Ak