Search code examples
vbaexcelexcel-indirect

Output of formula as cells for Excel's Indirect()

I'm using the INDIRECT() function to dynamically refer to worksheets. It seems to me that the function requires a known cell address as input of the formula in quotation marks. However, since I don't know the cell address in advance, I would like to input a cell address that is an output of another formula (e.g., VLOOKUP() or INDEX(MATCH()) ). Is there a way to do this?

Solution

  • Yes, you can compose the string required by INDIRECT() by concatenating formulas that return values that look like a cell address or parts of a cell address.

    For example, if your Vlookup or your Index/Match formula returns "A1", then you can use that like

    =Indirect(Vlookup(this,there,2,false))

    Or, if one formula returns "A" and the other formula returns "1", then combine the output of the two formulas with the & sign.

    =Indirect(Vlookup(columnLookup,Table,2,false)&Vlookup(RowLookup,Table,2,false))