Forum Discussion

Suresh Bopparaju's avatar
Suresh Bopparaju
Copper Contributor
Oct 05, 2017

Indirect references

Hello!

I wish to use the contents of a cell to form a part of the worksheet reference in a formula.

For example:

 

Cell A1 contains the string: PH21

Cell A2 contains the string: IFS

 

Elsewhere, I have a formula like =vlookup (A3, filename.worksheetName, 3, false);

 I want the "filename" to be the contents of cell A2 and worksheetName to be the contents of cell A1;

 

Please help me!

Excel Desktop
Formulas & Functions
  • Alun Davies's avatar
    Alun Davies
    Brass Contributor
    Oct 05, 2017
    The Basic Formula is
    =VLOOKUP(G5,INDIRECT(A1&A24&A4),2,FALSE)
    A4 is the Range
    however A1 needs to be [PH21.xlsx]
    and a2 needs to be IFS!
    or you can add these to the formula.
    try to get the contents of the Indirect in a cell so you can debug it first.

Resources