How to auto-insert multiple rows of data based on a lookup or index

Copper Contributor

Hello!

 

I am pretty new to the higher functions in Excel and need to solve an issue. We are working with equipment lists for massive projects. The final list might have 10's of thousands of lines. The typical workflow is to manually copy/paste rows of data per item from the Master equipment list to the project list as we discover we will need that equipment for a particular project.

 

I have figured a way to use Index and Match to pull data from one sheet to another after just typing in the Equipment ID value. Match looks for what has been typed into the ID field, searches for the same value in the Master list and pulls data from other columns in the same row. Not all the columns, as some items need to be manually entered.


The problem is, some of the equipment is described in 3-6 rows. For instance a 'computer bundle' is actually made up of four separate items, which each having a different model number, vendor, price, etc. So I need to be able to type in the ID and have it populate multiple rows of data if needed.

 

Is this possible? Would I be using an "IF" statement?

 

If it is not possible, is there a way find an item and replace with multiple rows of data, NOT touching certain fields in the row? For instance, the row may have fields populated with a Room number and an architectural identifier specific to that unit. Those need to stay. Ideally those could be populated in the rows that are pasted in.

Thanks so much and please let me know if clarification is needed.

 

Best,

 

Jen

0 Replies