Lookup An Entire Array?

Hello All!

I have a small workbook with 2 sheets that help me with payroll projections for my sales reps.

The first sheet is a summary sheet that auto calculates based on quite a few formulas. In the top left corner of this sheet (A1) I have the first name of the sales rep (with the first letter capitalized).

The second sheet is filled with 21 seperate small arrays. Each array is a specific payscale for each rep. I've selected each array and TITLED the array by the reps' first name.

My goal is to write a formula that allows me to reference the reps' name on sheet 1, and lookup the appropriate array. As I change the first name in the top left hand corner of sheet one (A1), I want the formula I write to reference the corresponding array on sheet 2.

Summary: (First name changes (cell A1 on sheet 1) and the array which the formula references changes since the title of each array is each reps' first name).

Please let me know if you have any thoughts!!!

Could you please upload your spreadsheet with any sensitive information removed. It makes it easier for us to help you.
Go Advanced --> Manage attachments

Hi crbird and welcome to the forum,

I didn't want to figure out where each of your VLookup tables were but I think I know where you want to go with this. I believe you need to use Indirect with Named Ranges. You would have a named range of cells on sheet 2 for each employee. Then you would name each of these blocks with the employee name. Then in your formulas you would use the indirect of what was in the name cell on sheet1 and it would look to the correct range on sheet2. See http://www.contextures.com/xlFunctions05.html and the Named Range section.

I hope this helps and is what you are asking for.

When you deleted stuff from your references tab, you got rid of some of your named ranges. That's fine, because you scrubbed it for sensitive information, but I have a question. Is the named range "FYRPayscal" being replaced with all the customized reps' payscales?

If that's the case then all you have to do is a find/replace for every instance of FYRPayscale, MGRPayscale, and EXPPayscale with Indirect($D$3). and make sure that the values in D3 are the same as the named ranges.