Searching for a value over multiple rows/columns and returning the first cell in the row

[Vertigo1]

I'm sure there's a stupid easy way to do what i'm trying to do, but I'm failing miserably so far. I'm relatively new to Excel, but I've been having a lot of success figuring out various function and formulas thanks to this forum and others. However, so far i'm stuck on this one.

Senario: (I've attached a demo workbook for reference)

I have two sheet in my workbook. The first sheet is a table of data which has various codes in column A, and circuit numbers in row 4. The second sheet is trying to fill cells with data from sheet 1. I need to search on a value specified in sheet 2, within the array of sheet 1. Assuming that value is located, I want to return the "Code" value from the first column of the row the value is found in. Secondly, the header of the array is a number, ranging from 1 to 6, I want to return that in a separate cell next to the first on sheet 2, separated by a cell with a dash in it. The end result being information being displayed as "Code-Number" to the user.

The final "issue" is that this same basic table, with different values may/may not be repeated on succeeding pages of the same sheet. I need to be able to search through and return data from however many pages of arrays exist.

If anyone needs more clarification, please let me know. I've attached a demo workbook to this post.

Thanks

Dave