• Status: Solved
• Priority: Medium
• Security: Public
• Views: 171

# 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
• 2
1 Solution

Commented:
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 Commented:
Thank-you so much for your help, this revelation has opened a whole new level for me.
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.