CODEWITHSUNDEEP
excelexcel-formula
Thiago Andrade
Thiago Andrade

Reputation: 51

Use a Dynamic Range in an Excel Formula

I have a collection of tables with similar names: mytable1, mytable2, mytable3...

I need to create a VLOOKUP formula that uses one table or another depending of the value of a named cell ( Cell "A1" = rngChoice ) , for example:

If rngChoice = 1 then use mytable1
If rngChoice = 2 then use mytable2
If rngChoice = 2 then use mytable2

I could use the formula:

 =IF(rngChoice=1;VLOOKUP("value";mytable1;"return column";FALSE);
  IF(rngChoice=2;VLOOKUP("value";mytable2;"return column";FALSE);
  IF(rngChoice=3;VLOOKUP("value";mytable3;"return column";FALSE) (...)

I can't use this formula because I may need to add or remove tables, so the formula should be changed every time, what's impossible to me.

What I need is something like:

=VLOOKUP("value";"mytable" & "table number";"return column";FALSE)

Upvotes: 1

Views: 49

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

Use INDIRECT to return the range:

=VLOOKUP("value";INDIRECT("mytable" & "table number");"return column";FALSE)

enter image description here

Upvotes: 4

Related Questions