CODEWITHSUNDEEP
excelvlookup
Sapan Gupta
Sapan Gupta

Reputation: 108

Dynamic Lookup array and Column index in Vlookup

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.

Upvotes: 0

Views: 498

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34285

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".

Upvotes: 1

Related Questions