• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use text as cell reference in vlookup formula?

shaikhrulez

Active Member
Hello everyone,

I am stuck in vlookup formula. I was wondering if there is a way I can concatenate a text and use it as a cell reference to other sheets in a vlookup formula?

Please refer image below.

I want to use values of first row as cell reference which will eventually link to specified sheet in my workbook.

For example

My existing formula is =VLOOKUP($A2,'04.07.2018'!$A:$C,3,0)

Is there a way I can use above formula like this. VLOOKUP($A2,'C1'!$A:$C,3,0)

so that when I drag my formula cell reference automatically adjust according to first row.

Thanks.

upload_2019-3-20_13-59-48.png
 
If this works: =VLOOKUP($A2,'04.07.2018'!$A:$C,3,0)
and you want to change the sheet name via a cell reference use the iduirect function

=VLOOKUP($A2,Indirect(C1 & "!$A:$C"),3,0)

Indirect takes the text from A1, adds the "!$A:$C" to it and converts the resultant text to a proper cell reference
 
If this works: =VLOOKUP($A2,'04.07.2018'!$A:$C,3,0)
and you want to change the sheet name via a cell reference use the iduirect function

=VLOOKUP($A2,Indirect(C1 & "!$A:$C"),3,0)

Indirect takes the text from A1, adds the "!$A:$C" to it and converts the resultant text to a proper cell reference

Thanks @Hui for prompt response.

The formula worked fine with date as 04.07.2018 but not working when I drag it further. Please have a look.

upload_2019-3-20_14-19-41.png
 
The Worksheets need to match the dates formats or Dates Formats need to match the Worksheet Names

Note the difference in the format of Cell D1 compared to C1
 
The format is same, even I have copied the sheets name and made this new sheet to pull data from all sheets.

Have a look again.

upload_2019-3-20_15-9-1.png
 
When I am entering the formula manually

i.e. =VLOOKUP($A2,'05-07-2018'!$A:$C,3,0)

The formula is fetching data, don't know why it isn't through INDIRECT.
 

Attachments

  • upload_2019-3-20_15-11-31.png
    upload_2019-3-20_15-11-31.png
    152.8 KB · Views: 1
  • upload_2019-3-20_15-12-50.png
    upload_2019-3-20_15-12-50.png
    152.9 KB · Views: 2
I suspect it is because you have Dates in Row 1

Maybe try:
=VLOOKUP($A2,Indirect("'" & C1 & "'!$A:$C"),3,0)
or
=VLOOKUP($A2,Indirect("'" & text(C1,"dd-mm-yyyy") & "'!$A:$C"),3,0)

Then you will have to rename all the worksheets with -'s not .'s

Note the extra ' included in the fomulas
 
I don't mind entering two different formula if required.

Still getting same error. I have attached file. please have a look.
 

Attachments

  • July-2018.xlsx
    82.2 KB · Views: 4
These two formulas are now working fine. Thanks a lot @Hui :)

=VLOOKUP($A2,Indirect("'" & C1 & "'!$A:$C"),3,0)
or
=VLOOKUP($A2,Indirect("'" & text(C1,"dd-mm-yyyy") & "'!$A:$C"),3,0)
 
Back
Top