The Hardest Thing I've Ever Done . . . with a VLOOKUP

I'm only putting this on my blog on the off chance that somebody someday googles this topic (and not to prove that I'm exceptionally nerdy).

How to get Excel to look up information from identical tables on different tabs/worksheets
(I'm not going to explain how VLOOKUPs work, so if you've never used one this probably won't make any sense)

Just so we're on the same page, here's the basic VLOOKUP formula:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Here's the problem (in simplified terms):
I have ten different companies that I'll need to get information for when preparing state tax returns. I wanted a summary worksheet in Excel to automatically fill in a company's information for any particular state just by typing that information (state and company) into two cells at the top of the worksheet. It was really easy to include a cell reference in the lookup_value part of the formula. What really had me stumped was figuring out how to get the table_array portion to work the same way.

The answer is two-fold:

FIRST - In Excel, you can select a range of cells and give it a name (like “CO1”) by going to the Insert menu, Name, Define. Once the name for that range is defined, Excel will know what you're referring to when you use that name in a formula. So you could get a VLOOKUP to work just by typing in =VLOOKUP($B$2,CO1,2,FALSE) and it knows to look for whatever value is in cell B2 in the CO1 range and return the value in column 2. That's the first half of the solution.

SECOND - The second problem was that I really wanted whoever was using the summary worksheet to be able to just type in the company abbreviation and have Excel know which tab to look at in the workbook, but Excel didn’t like just having a plain old cell reference in the “Table_Array” field of the function. I scoured the Help menu and the Internet and finally found someone who recommended using the INDIRECT function in a similar situation. Here's how I was ultimately able to get it to work: =VLOOKUP($B$2,INDIRECT($B$1),2,FALSE). I still have no idea what the INDIRECT function is intended to do, but it got the formula to work the way I wanted it to and that’s all I cared about.

You should note that this worked for me because all of the tabs of company information were identical, if yours aren't identical it could cause some problems.

My Release

In a shocking turn of events, I was released from my calling in the YW yesterday and called to be the 1st counselor in the Primary Presidency. Asi es la vida (that's life). So my blog of ideas for YW activities has been cut down in the prime of life. ;)