• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Replacing an array name with a variable

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
Bob Barnes
Asked:
Bob Barnes
  • 2
1 Solution
 
gowflowCommented:
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
 
Bob BarnesAuthor Commented:
Thank-you so much for your help, this revelation has opened a whole new level for me.
0
 
gowflowCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now