Sheet Summary - same sheet VLOOKUP with TODAY()

Matthew Best

Hi,

My Column Headers are:

Date, Total Opened Cases, Total Closed Cases, Total Backlog.

I am attempting, and failing, to use the following variations of a VLOOKUP formula. Can someone help??

=VLOOKUP(TODAY(), {Date}:{Total Backlog}, 4, false) = #UNPARSEABLE

=VLOOKUP(TODAY(), {Date:Total Backlog}, 4, false) = #INVALID REF

=VLOOKUP(TODAY(), Date:Total Backlog, 4, false) = #UNPARSEABLE

Nick Korna

Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:

=VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)

If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.

Nick Korna

Hi @Matthew Best,

The middle one looks to be correct formula wise, but have you set the reference correctly when putting the formula in? If you're trying to create the cross sheet reference without doing this it won't work.

If you're not sure how, this should show you:

Click on this when entering the formula. On the screen which opens, find your sheet to reference in the list on the left (you can search by its name to find it) and highlight the columns required and rename the Sheet reference name (as it will default to the sheet name plus range and a number):

Hit the OK button in the bottom right of this screen and finish the VLOOKUP and everything should work:

Hope this helps, but if it's something else then please let us know so we can try and get it fixed.

Matthew Best

Thank you for your answer @Nick Korna.

However, the 'Reference Another Sheet' will not allow me to select the sheet I am currently in. It only allows me to select an different sheet?

Nick Korna

Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:

=VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)

If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.

Matthew Best

Thanks this worked. But I don't understand why. Why doesn't Date have [] but Total Backlog does have them?

Nick Korna

The square brackets are for if your column header has a space in. So "Date" wouldn't need them, but "Total Backlog" does. If "Date" was "Start Date" or "End Date", then this would also need the [ ] around it for the reference.

Matthew Best

Thanks much appreciated. 👍️

Nick Korna

No problem, happy to have helped out! 😊