I have two worksheets. One has a list of brokers and 50 columns for the different states. Each state column contains the license numbers for the brokers who are licensed for that state. If the broker isn't licensed, the cell in that row is blank. Or, it could say "Pending" or "Inactive."

My second worksheet has a separate tab for each state, and lists the brokers and their license numbers. The same information displays, but it's just organized differently.

I need to set it up so that we only input the information on one of the worksheets.

I thought an array was the best way to go, but I never used an array before, and I couldn't seem to get it to work between two worksheets. That's just one problem.

I've been reading the About.com tutorial on how to do this, and as I understand it, the VLOOKUP dialog requires me to put the first column in Lookup_value. But my lookup value is going to be whatever is in the State column off to the right. So I'm confused about what goes in the Look_up value field, and what goes in the Col_index_num field. I'm probably confused about the two remaining fields as well. The examples in the tutorial don't match up with what I'm trying to do, and I can't wrap my head around the logic yet to be able to customize.

And then, of course, there's the issue of sending the information off to that second worksheet.

Am I on the right track? Or should I be doing something different?

Thank you!