Dynamic Lookup array and Column index in Vlookup

Question

I have a workbook with 12 sheets named as January,February till December. In every sheet, I have the attendance of employees for each day of the month. Now the challenge is I want to compile the list in one worksheet which has name of employees in column A and in row I have dates from Jan 1 to dec 31.

Can I use a single Vlookup(), or Match() and Index() formula to get result? I have tried it and ended up with the formula:

=VLOOKUP($A:$A,'Jan '!A:AK,COLUMNS($B1:B1)+1,0)

However I want Sheet Name to be changed to Feb after 31 occurrence and so on, Also range of column $B1:B1 restarts from same after 31 occurrence.

I hope my query is clear. Please help.

Tom Sharpe · Accepted Answer · 2018-08-14 09:46:55Z

1

The equivalent of your formula if you have separate sheets for each month would be

=VLOOKUP($A2,INDIRECT(TEXT($B$1+COLUMNS($B:B)-1,"mmmm")&"!A:AK"),DAY($B$1+COLUMNS($B:B)-1)+1,FALSE)

starting in B2 and assuming you have the first day of the year in B1.

But you can make it easier if you have headings in the first row of the master sheet for each date of the year

=VLOOKUP($A2,INDIRECT(TEXT(B$1,"mmmm")&"!A:AK"),DAY(B$1)+1,FALSE)

This is for sheets called January, February etc. If the names are Jan, Feb etc. then change "mmmm" to "mmm".

edited Aug 14, 2018 at 9:46

answered Aug 14, 2018 at 9:40

Tom Sharpe

35.2k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Lookup array and Column index in Vlookup

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related