Search code examples
excelvlookupworksheet

Referencing a worksheet through a cell value - Excel

I'm doing a vlookup with the matrix to search in another worksheet which name is may-18 (I have other worksheets with name apr-18, mar-18, feb-18, jan-18). The problem I'm facing is I want to refer the worksheet through the value of a cell (In cell A1 I have written 'may-18). For example:

=VLOOKUP(C4,'may-18'!$C$6:$BY$28,7,FALSO) # Current
=VLOOKUP(C4,'apr-18'!$C$6:$BY$28,7,FALSO) # Current
=VLOOKUP(C4,'mar-18'!$C$6:$BY$28,7,FALSO) # Current
=VLOOKUP(C4,'feb-18'!$C$6:$BY$28,7,FALSO) # Current
=VLOOKUP(C4,'jan-18'!$C$6:$BY$28,7,FALSO) # Current

=VLOOKUP(C4,'A1'!$C$6:$BY$28,7,FALSO) # Looking for this
Solution

  • You can use INDIRECT():

    =VLOOKUP(C4,INDIRECT("'"&$A$1&"'!$C$6:$BY$28"),7,FALSO)

    Note: This assumes A1 has may-18, apr-18, etc. in the cell (note the lack of a leading apostrophe, which you put in your OP. I assumed that is a typo).