Checking a column entries for specific text and reference cells not containing that text

[Zsenialis]

Hello!

I have a really unique excel task I couldn't figure out and hope for help here.

I have a list of room types and their attributes (total area, furniture, etc). This sheet has a format that has to be kept, however to be able import the content to a different program I need a simplyfied, plain excel sheet. In order to achieve this I created a new sheet and referenced the room names there, and using Vlookup I also matched the related attributes. The problem is that there are several unnecessary rows I can't get rid of, such as "For new types add new row", etc and merged rows containing one room type have "0"s (obviously only the first cell has data the other merged cells are empty). I tried to use an IF and OR combination: if the unnecessary data is present in a cell then reference the next row, else reference current row. The problem is that with this function combination I can only cover a finite number of skipped rows and after 3 IFs I couldn't follow the function. Also there could be even 8-10 (or more) merged rows that have to be skipped. Is there a way to create this function to be indefinite (basically to tell the program to skip the unnecessary row until it finds one with relevant data) or a solution with a different approach?

If something is not clear about the description, please let me know and I describe it further!

Thanks in advance!

 

[Zsenialis]

I built this on the same worksheet since you haven't provided the name of your first worksheet. If you start this off in column G of the first worksheet to test it, you should then be able to move it onto the second worksheet, and the references will update. Enter the formula in G2, and fill through I2, and then down as needed:

Thank you for the help! Yesterday I posted the question from my private laptop from home and I didn't have access to the original document in my work PC, but I'will try it at work and let you know how it works. Thanks again!