Formula Challenge

dwhims

Board Regular
Joined
Sep 10, 2003
Messages
155
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
=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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,820
Members
452,426
Latest member
cmachael

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top