Excel, Finding Data in a Column, then posting the data in an adjoining column

Question

Excel Visual Example

I am looking to setup a formula to search Sheet 1, Column A for a specific term, in this case "PPG", then write the data from Column B of Sheet 1 into Column A of Sheet 2.

Sheet 2, shows the list I would like to have displayed. How do I write a formula to pull the data from "Sheet 1", find the instances of PPG, pull the data in Column B and write then in Column A of "Sheet 2", then go to the subsequent lines to find other instances? Do I need to write a Macro to run?

I have =IF(A3=PPG,"B3","") however I'm not sure how to write it to find the next instance so if I have several lines between PPG data, it doesn't show up in the output.

Answer 1

try,

=index(sheet1!b:b, aggregate(15, 6, row($3:$6)/(sheet1!a$3:a$6="ppg"), row(1:1)))

fill down for second, third, etc.