Looking up information from another table. For all instances of "X" word in column 2, return information in column 1

I am currently working with Excel 2016.

My objective is to find all instances of a word in a specific column in another table, and pull the information from a different column in that table to the new table.

For Example:

Lookup table
Column 1 (ASSET)      Column 2 (Equipment)       Column...

XXX-BO-01             Boiler
XXX-BO-02             Boiler
XXX-BO-03             Boiler
XXX-BO-04             Boiler
XXX-P-01              Pump
XXX-P-02              Pump
XXX-S-01              Sink
XXX-H-01              Heater
etc...                etc...

I am looking for a formula I can use in another table to look in this table for every instance of "Boiler" and return the unique values in Column 1. The new table will be say, 20 rows, I would like the formula to be present in every row in the appropriate column (so if more Boilers are added in the lookup table the new table will automatically populate that information), but return a 0 whenever it has already found all the unique instances of "Boiler" and returned the requested value.

The new table would appear as:

Column 1            Column 2          Column...
XXX-BO-01
XXX-BO-02
XXX-BO-03
XXX-BO-04
0
0
0
etc...

I am also looking for a version of that formula which will for with multiple variables, such as if I wanted to pull all the information for both boilers and pumps.

Thanks very much in advance.

PS. Things I have tried:

(=LOOKUP("Boiler",Table31314415090234[Equipment],Table31314415090234[Asset])

=LOOKUP(2,1/SEARCH("Boiler",Table31314415090234[Equipment]),Table31314415090234[Asset])

Both of these only return the last instance from the lookup table

=INDEX(Table31314415090234[Equipment],SMALL(IF("Boiler",Table31314415090234[Asset],0)))

This is incomplete and does not work

=INDEX(Table31314415090234[Asset], SMALL(IF("Boiler"=Table31314415090234[Equipment], ROW(Table31314415090234[Equipment])-ROW(Table31314415090234[Asset])+1), ROW(1:1)))

This returns "#VALUE" I attempted to utilize another answer doing something similar but could not quite crack it.

=VLOOKUP("Boiler",Table31314415090234,Table31314415090234[Asset],FALSE)

Lastly I tried this but just get "#N/A"

Paste this (without the headers) in a new sheet:

POST-COMMENTS EDIT

The AGGREGATE function (as written in this case) returns the row reference of the results which is used in the INDEX function. Since the table is offset from the top of the sheet, one must correct for the row difference between row 1 and the header's row. In order to achieve this, one can either change the INDEX range of reference (say by considering the whole column that hosts the desired column inside the table) or compensate the result of the AGGREGATE result. The following formula (developed from the one you have provided) use the latter technique:

 =IF(ROWS($A$6:A6)>COUNTIF(Table31314415090234[Equipment],"Boiler"),0,INDEX(Table31314415090234[Asset],AGGREGATE(15,6,ROW(Table31314415090234[Equipment])/(Table31314415090234[Equipment]="Boiler"),ROWS($A$6:A6))-ROW(Table31314415090234[Asset])+1))

Test it and send feedback.