Relating Cells to text within a column

[Nemoibis]

I'm using Excel 2010, and am trying to work with imported sheets with a few thousand rows that don't have a set structure of row orders, but do have set columns and groupings. So I'm trying to create a formula that will be able to find contained text within a range, and find another cell correlative to the position of the found range cell as a result.

So for example, search for if a cell contains the phrase "Class 2957b" in range x, and if that word is found in cell N137, then go down 2 rows, and over 3 columns to q139 for the result. Essentially, if range contains text in cell x, then cell y with a position relative to cell x equals cell z.

I'm not sure where to really go with this in order to return a related cell result. Any ideas?

 

[FDibbins]

Sounds like you need something like...

=INDEX(N:N,match(Z1,A:A,0)+2)

If that does not work for you, please provide some sample data

 

[Nemoibis]

Hey FDibbins, thanks for the reply! So here's an example of how the data is laid out in the sheets.

So, week 1 and week 2 both have Dogs Birds and Cats, but in different orders, and with different colors. The columns for both weeks are also in different orders, and have similar terminology, but never exactly the same. I have no control over how these sheets are arranged when I receive them. What I need to find is both the value for long orange dogs, and the total value for all yellow short animals. The closest I've figured out how to do is something like

=IF(ISNUMBER(SEARCH("Dog",$A5)),IF(ISNUMBER(SEARCH("Long",B$4)),IF(ISERR(SEARCH("Short",B$4)),A6,),),)

And just copy and pasting through just as many rows and columns as the sheet itself has. However, not only will this will become completely useless whenever the order of Orange changes, but it's also making the entire workbook unreasonably large in file size, as in reality, I'm searching through several thousand rows. Any help or insight would be greatly appreciated!