VLOOKUP

Eric Kreitman

I am trying to use VLOOKUP to determine the column to pull a value from. My columns are basically months (1 through 12) and I need to use the 'current month' to determine the number 1-12 to get the value "@row". I am unsure if VLOOKUP can provide the functionality I am looking for so please let me know if I am asking for something it cannot do.

With Month(Today) = 11, I want the value "@row" from my Column Name "11" to be placed in the "Hours" column.

I am currently getting a "#NO MATCH" error.

Vlookup.pdf

Nick Korna

I would use the INDEX/MATCH for preference:

=INDEX([1]:[12], MATCH([Client Name]@row, [Client Name]:[Client Name]), MONTH(TODAY()))

If you wanted to stick with VLOOKUP:

=VLOOKUP([Client Name]@row, [Client Name]:[Hours this month], MONTH(TODAY()) + 3)

Sample data:

The INDEX has the advantage if you need to add in additional columns near the start for whatever reason you would not need to update the formula with the column reference (unless you put another column in the middle of your month range).

If you were wanting to put these in a dashboard, it may be simpler to have them as sheet summaries for a sheet summary report. For example:

=INDEX([1]:[12], MATCH("Client A", [Client Name]:[Client Name]), MONTH(TODAY())

You can then use the summaries to produce report(s) for the dashboard.

If you have something with a sum of their current hours worked in a given month, you can use a cross sheet formula to give the hours in that month remaining.

For example if you had a list of hours worked by client/date/hours worked and wanted to see how many hours Client A had remaining:

=INDEX({1-12}, MATCH("Client A", {Client Name}), MONTH(TODAY())) - SUMIFS([Hours Worked]:[Hours Worked], Date:Date, MONTH(@cell) = MONTH(TODAY()), [Client Name]:[Client Name], "Client A")

Again, these would be easy to have as sheet summaries to make easy to put into a report.

Hope this gives you some ideas - let me know if you've any queries still.

Nick Korna

Hi @Eric Kreitman,

What value are you using as the search value in the VLOOKUP? You can definitely add in something to get the month's number in this calculation.

For example, if your columns are the lookup value followed by the 12 monthly ones (called 1-12):

=VLOOKUP("A", [Lookup value]@row:[12]@row, MONTH(TODAY())+1)

Alternatively you could use an INDEX/MATCH to accomplish the same result without needing the lookup value adjacent to the monthly numbers:

=INDEX([1]:[12], MATCH("A", [Lookup value]:[Lookup value]), MONTH(TODAY()))

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

Eric Kreitman

Hi @Nick Korna,

Thank you for the note and the suggestions. I may not be explaining this correctly so let me try giving the full background of what I am trying to do.

The list shown is for tracking the number of support hours by month our clients sign up for and as highlighted below the numbers are often not the same month over month. What I am trying to do here is set a formula to use the "Current Month" (or MONTH(TODAY()) formula) to determine which column (1-12) has the value in a specific row (using @row) for the current month. With the current month being 11, I am trying to get the formula to look at column labeled "11" and put that number in the "Hours" column. I will then use this in a dashboard to provide a reminder to my client how many hours they have signed up for in a specific month.

I am opened to other ideas on how to accomplish this, starting over is not a problem if there are easier/wiser ways to do this.

Thank you.

Eric

Nick Korna

I would use the INDEX/MATCH for preference:

=INDEX([1]:[12], MATCH([Client Name]@row, [Client Name]:[Client Name]), MONTH(TODAY()))

If you wanted to stick with VLOOKUP:

=VLOOKUP([Client Name]@row, [Client Name]:[Hours this month], MONTH(TODAY()) + 3)

Sample data:

The INDEX has the advantage if you need to add in additional columns near the start for whatever reason you would not need to update the formula with the column reference (unless you put another column in the middle of your month range).

If you were wanting to put these in a dashboard, it may be simpler to have them as sheet summaries for a sheet summary report. For example:

=INDEX([1]:[12], MATCH("Client A", [Client Name]:[Client Name]), MONTH(TODAY())

You can then use the summaries to produce report(s) for the dashboard.

If you have something with a sum of their current hours worked in a given month, you can use a cross sheet formula to give the hours in that month remaining.

For example if you had a list of hours worked by client/date/hours worked and wanted to see how many hours Client A had remaining:

=INDEX({1-12}, MATCH("Client A", {Client Name}), MONTH(TODAY())) - SUMIFS([Hours Worked]:[Hours Worked], Date:Date, MONTH(@cell) = MONTH(TODAY()), [Client Name]:[Client Name], "Client A")

Again, these would be easy to have as sheet summaries to make easy to put into a report.

Hope this gives you some ideas - let me know if you've any queries still.

Eric Kreitman

@Nick Korna

Thank you for the additional guidance and solution options. The summary approach is a great! Actual hours used are being pulled from Resource Management (time entry) weekly and stored in a Smartsheet Grid. This gives our client both the summary of hours used as well as a more detailed report from RM on hours and notes.

Thank you again for your help.

Regards,

Eric