CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-indirect
okmred
okmred

Reputation: 147

Use day (cell with date) in a VLOOKUP

I have a main sheet with my data and 31 possible sheets with additional data used for input for various calculations. I want to use a VLOOKUP to grab the correct data, but Iml having a hard time with the additional sheet reference. I'm hoping to make this a dynamic formula based on the date in the third column on the main sheet.

This is what currently works:

=VLOOKUP(A2,'1'!$A$1:$B$153, 2,TRUE )

I'm hoping to get something like the below to work:

=VLOOKUP(A2,' "Day(C2)" '!$A$1:$B$153, 2,TRUE )

where C2 is my date, so then it would grab the day from that date and do the lookup against that sheet and range.

Upvotes: 1

Views: 548

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

You need to use INDIRECT. 

=VLOOKUP(A2,INDIRECT("'" & "1" & "'!$A$1:$B$153"),2,TRUE)

Now simply replace "1" with Day(C2)

=VLOOKUP(A2,INDIRECT("'" & DAY(C2) & "'!$A$1:$B$153"),2,TRUE)

Screenshot

enter image description here

Upvotes: 1

Related Questions