Finding text string within Range and returning Row/Column number

Hi, I have problem with finding text from a range. I tried the following formula, but my text has trailing spaces afterwards so it's not finding it. I tried inserting an asterix as a wildcard after the text to find but again it came back with an error.

B1: =ADDRESS(MAX(INDEX((B2:D100="PET*")*ROW(B2:D100),0)),MAX(INDEX((B2:D100="PET*")*COLUMN(B2:D100),0)),4)


What I want to achieve:

I want to be able to import a report into my spreadsheet which may, or may not have the required header names in it. For example, I may have a report WITH header names PET, NAME, AGE ("_" characters denote trailing spaces):

A B C D
1
2 PET___ NAME_ AGE__
3 Cat Snowy 7
4 Dog Tommy 3
5 Cat Felix 10


Or WITHOUT header names:

A B C D
1
2
3 Cat Snowy 7
4 Dog Tommy 3
5 Cat Felix 10

The report could be pasted in randomly on the sheet, i.e. starting on cell D8.

Then I want to..
1. Find the text "PET" from a cell within the range B2:D100
2. If it finds "PET" then return the row number in cell B1 (use this row number as the starting point for columns C and D, i.e if row number of "PET" is 5 then retrieve text from cell C5 "NAME" to C1 and cell D5 "AGE" to D1)
3. If it does not find "PET" then we create a header of the same name
4. With my found, or newly created header names, I can then determine what from the report is a header and what isn't, and treat them accordingly when carrying out lookups on the different elements of the report.

I hope that makes sense, and that someone can help.

Many Thanks, Ian

Thanks very much abousetta. Brilliant, works like a treat!