Looking for a text string and putting that cell value into a table

Hello and thank you for this forum. This site has been a invaluable to me as I have struggled to learn Excel. My problem seems straightforward but I have not been able to find a reliable way to solve my problem. I am trying to search through several columns for a specific text string. When the cell with that particular text is found, I need to take those cell contents and put them in a new table. The problem is that the text I am searching for does not always appear in the same exact location, and sometimes the information that I want to put into my new table is located in a cell adjacent to the cell containing the text string. I have attached an excel file that gives an example of what I am searching for. The cells with red text are the ones that I want to only return the contents to my new table. The cells with blact text are the ones that I need the adjoining cells to the right to be put into the new table. I am searching for the specific text in column G and when I am finished, I want my results to look like the information in column H. Thanks for your help. - Tom

Wow. Thank you so much for your help. I knew that it was going to be a little complicated but I had no idea that it would require such a complex formula. You are right that it would be much more simple to set the data up differently but unfortunately the data source (a multiple listing service) only exports the data as a HTML form in that format. My original thought was that if there was a function that would find the correct text string, I could then put it into another cell then use a True / False argument to ask if "Bank ownedNo" then the "No" to help with the misformatted data. Why did the last two cells in H20 and H21 return a "#REF! and #VALUE! ? Is it because it did not return an exact match for the text string?

Thanks again for your response. Any ideas that you might have would be great.

Using two helper columns (I and J, hidden for a cleaner look), i got the following result, see attachment.

That is perfect. Thank you very much for your help.