Having the table array in vlookup change to a different tab for each cell

[cole416]

I am currently making a table with numbers that I am pulling from different tabs in my workbook through VLOOKUP. Each row going down is getting data from a different tab, but the table values are all the same, and the text I am looking for is all the same.

Example:

6.1	=VLOOKUP("Orange",'6.1'!$A$12:AL$180,23,FALSE)
6.2	=VLOOKUP("Orange",'6.2'!$A$12:AL$180,23,FALSE)
6.3	=VLOOKUP("Orange",'6.3'!$A$12:AL$180,23,FALSE)
6.4	=VLOOKUP("Orange",'6.4'!$A$12:AL$180,23,FALSE)

I am trying to make it easier for me to do this over many different cells so that I don't have to manually type in 6.5, 6.6, 6.7, etc. because this will be for hundreds of rows.

I tried referencing the cell that has the text of "6.1" as part of the table array but it wasn't working for me. Is there any way I could easily make this smooth to do for many cells?

Please let me know if you guys need any clarification with what I am asking

 

[cole416]

I just want to note that the names of each tab they are pulling from are the exact same as what I put in column A.

 

[Automatrix]

Try this (supposing your example starts in A1):

=VLOOKUP("Orange",INDIRECT("'"&A1&"'!$A$12:AL$180"),23,FALSE)

 

[cole416]

Try this (supposing your example starts in A1):

=VLOOKUP("Orange",INDIRECT("'"&A1&"'!$A$12:AL$180"),23,FALSE)

Worked perfectly. Thank you so much!

 

[Automatrix]

Worked perfectly. Thank you so much!

INDIRECT is a weird and wonderful formula. By the way, since you have Office 365, you should consider using the new and improved XLOOKUP instead of VLOOKUP.