Forum Discussion
ladint
Sep 12, 2024Copper Contributor
Help with VLOOKUP formula for yesterday's data please
Hi! I'm asking for help creating a formula that pulls Yesterday's data into another spreadsheet. I pull an excel report that lists by column today's data, yesterday's data, the previous day's data, ...
m_tarler
Sep 12, 2024Bronze Contributor
this is unclear what you are presently doing and/or what isn't working for you.
I assume you are using the TODAY() function? So yesterday is TODAY()-1
if you need it to be workdays then you can use WORKDAY(TODAY(),-1) and even add a reference to holidays if wanted.
I would also like to mention if you have Excel365 that you shoud consider using FILTER. I would build a function example closer to your application but you didn't include enough so a more general example might be:
=FILTER( full_table , (full_table[#Headers]>=TODAY()-1) )
that is simplified and won't actually work as is for a number of reasons but the general idea
if the source is not a defined table then you need to use something like INDEX(full_table,1,0) or TAKE(full_table,1) but if it is a defined table then you need to convert those headers from text to values so IFERROR( --full_table[#Headers], 0) or something like that.
and lastely this assumes you don't have future dates but if you do then you want
(full_table[Headers]>=Today()-1)*(full_Table[Headers]<=Today()) for the conditional
I assume you are using the TODAY() function? So yesterday is TODAY()-1
if you need it to be workdays then you can use WORKDAY(TODAY(),-1) and even add a reference to holidays if wanted.
I would also like to mention if you have Excel365 that you shoud consider using FILTER. I would build a function example closer to your application but you didn't include enough so a more general example might be:
=FILTER( full_table , (full_table[#Headers]>=TODAY()-1) )
that is simplified and won't actually work as is for a number of reasons but the general idea
if the source is not a defined table then you need to use something like INDEX(full_table,1,0) or TAKE(full_table,1) but if it is a defined table then you need to convert those headers from text to values so IFERROR( --full_table[#Headers], 0) or something like that.
and lastely this assumes you don't have future dates but if you do then you want
(full_table[Headers]>=Today()-1)*(full_Table[Headers]<=Today()) for the conditional