Solved

Replacing an array name with a variable

Posted on 2014-03-17
3
155 Views
Last Modified: 2014-03-18
I have included a simple spreadsheet with 4 named arrays Products_A to Products_D, you will see the Cost in cells B11 to B14 alter as the cell A1 is changed from A to B to C to D. I want to edit the formulas in B11 to B14 to include a variable in place of the array name, in an effort to shorten the fomula, as there are many more product ranges and prices to be added. Presently the formula is as follows:
=IF($A$1="A",VLOOKUP(A11,Products_A,2,FALSE),IF($A$1="B",VLOOKUP(A11,Products_B,2,FALSE),IF($A$1="C",VLOOKUP(A11,Products_C,2,FALSE),IF($A$1="D",VLOOKUP(A11,Products_D,2,FALSE),"Error"))))
I want to change the formula to =IF($A$1="A",VLOOKUP(A11,D3,2,FALSE) etc etc where D3 changes when the value of D1 alters, but this returns #N/A as indicated in D11. Sorry if it's not too clear but I've tried to explain it as best I can.
Test01.xlsx
0
Comment
Question by:Allpurple
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39935094
replace in your formula
D3 by INDIRECT(D3)

it should be this:
=IF($A$1="A",VLOOKUP(A11,INDIRECT(D3),2,FALSE),IF($A$1="B",VLOOKUP(A11,INDIRECT(D3),2,FALSE),IF($A$1="C",VLOOKUP(A11,INDIRECT(D3),2,FALSE),IF($A$1="D",VLOOKUP(A11,INDIRECT(D3),2,FALSE),"Error"))))

gowflow
0
 

Author Closing Comment

by:Allpurple
ID: 39936521
Thank-you so much for your help, this revelation has opened a whole new level for me.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39936527
Glad to be a source of inspiration ! pls feel free to put a link in here for any new question you may need help with.
gowflow
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Viewers will learn various types of data validation for different data types in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now