Using a combo of Index and Match is actually a pretty good way to go when searching for something.

Example:

Let’s say your big table of info is on “Sheet1”. Your 20 company names are in cells A2:A21. In this case, I’ll be using the company names of 1, 2, 3…, 20 in those cells. Your 200 dates would be in cells B1:GS1 with the share prices within those parameters. For dates, I started with 1/1/2007 in cell B1 and incremented by 1 day in each following cell.

Now, let’s go to “Sheet2” where we will do your formula to look up the information. There are a couple of ways to do this. You can write the formula to look up something specific you mention in the formula or you can be a little more dynamic and have the formula look up something referenced in a cell. Since the dynamic way is a lot easier to re-use when you want to look up different values, we’ll go that way.

The formula that includes the specific items you want to look up:

In this example, I want to look up the value for Company 5 on 1/6/2007.

On Sheet2, cell A2, I would type in the number 5. In cell B1, I typed in 1/6/2007 (formatted to match how the dates are displayed on Sheet1.

Then, in cell B2, put in the following formula -

=(INDEX(Sheet1!$B$2:$P$21,(MATCH($B$1,Sheet1!$1:$1,FALSE)),(MATCH($A$2,Sheet1!$A:$A,FALSE))))

Clear as mud?

The index formula essentially is a search by row and column. Adding in the match lets you be a little more flexible in what you want to search for.

Breaking down the formula:

=(INDEX(Sheet1!$B$2:$P$21, is simply defining the array of data results you want to search and pull from

(MATCH($B$1,Sheet1!$1:$1,FALSE)), is where you set up the column portion of the array you want to match. You are saying look in cell B1. Take that value, go to the next sheet and find where that value is in row 1.

(MATCH($A$2,Sheet1!$A:$A,FALSE)))) is where you are saying look in cell A2. Find that value, go to the next sheet, look in column A. Then, the formula pulls whatever is at the intersection of your two matches and returns that value.

A couple of links that might help you more than my muddled explanation:

Mr. Excel

Excel User