Formula Challenge

[dwhims]

Hi all!

Here's what I'm looking for. I need a vlookup formula that works like this:
=vlookup(A3,Z3,B1,false)

where:
Col A = Acct #
Col Z = Named Range Name
Col B = current formula
Row 1 = column index #

And here's a little more explanation of what I'm doing and why:

I have a large workbook containing 19 sheets. Sheet 1 is my Master, and sheets 2-18 contain the same information, broken out by Vendor. My staff updates the individual sheets 2-18, but don't touch the Master. However, the Master pulls data from each of the individual sheets, based on Account Number. Right now, this is accomplished using vlookup formulas, which were manually entered, and uses Named Ranges as theTable Array part of the formula. All is working fine.

However, we have reached a stage of the project where some accounts are going to be moved from Vendor A to Vendor B, etc. As my Master works now, each formula for each moved account will have to be changed so the Table Array reflects the new location of the data.

I've added a column to my Master which contains the Named Range where the account information appears. What I would like to do is have my vlookup formula reference this column, so that if I change the location of the account information in that column, the formula will pick up the new location.

Is this doable? Is there an easier way to accomplish the same thing?

Thanks in advance for any advice at all!

 

[cfree36]

=vlookup(A3,indirect(Z3),B1,0)

A3 what you are looking up
Z3 the named range where you are looking
B1 the column index number
0 = false

 

[dwhims]

YOU ARE AWSOME!!!!!

That worked like a charm! I think it took me longer to type my problem that it will take me to fix my spreadsheet of 70,000 formulas!!!

Thanks so much, and Happy Thanksgiving!