Is this possible? Vlookup with dynamic reference tab

[DarrenK]

Hello,

Apologies if I am asking this incorrectly but I'd like to know if I can use a VLOOKUP formula that has a dynamic worksheet tab reference?

Here's what I have:

12 tabs, each with a different month of the year-first 3 letters (JAN, FEB, MAR, etc). Each tab has raw data for all employees (~100) and the metrics they achieved for that month.

Tab #13 ("3 Months Data") has all employees listed down Column 2 with their Employee IDs in Column 1.
In Column 3, OCT's attendance data appears with the column heading "OCT" above all values. The same for NOV and DEC as those are the last full months to end. I attempted to copy/paste below. The formula I have in each cell to pull from the raw data tabs is: =IFERROR(VLOOKUP($B5,Oct!$G:$Z,11,0),"---") as I wanted errors to display dashes, not blanks.

My question (after all of that) is can I make the VLOOKUP formula reference the specific monthly tab, not by actually having the tab name written in the formula, but by whatever is written in the column heading? I did research and I think it has something to do with INDIRECT functions but I've never worked with those and wouldn't know where to begin as the VLOOKUP is as complicated as I can probably get on my own.

So if someone types JAN into Cell C2, the VLOOKUP formula in all cells that follow would look for the tab labeled the same as what's entered into C2. I hope that made sense.
[TABLE="width: 718"]
<colgroup><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ATTENDANCE[/TD]
[TD="colspan: 3"]PERFORMANCE[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]Emp ID[/TD]
[TD]Agent Name[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]12345[/TD]
[TD]Smith, John[/TD]
[TD="align: right"]99%[/TD]
[TD="align: right"]98%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]67890[/TD]
[TD]Thomas, Joan[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]98%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]96%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

If I can avoid using VBA that would be perfect.

 

[FormR]

So if someone types JAN into Cell C2, the VLOOKUP formula in all cells that follow would look for the tab labeled the same as what's entered into C2.

Hi, it would look something like this.

=IFERROR(VLOOKUP($B5,INDIRECT("'"&C2&"'!$G:$Z"),11,0),"---")

 

[DarrenK]

OMG that's awesome! Thank you for the quick response FormR. That's exactly what I needed. It works flawlessly in the cells I've tested. I'll let you know if I come across any kinks.