Help with VLOOKUP formula for yesterday's data please

[ladint]

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, and so on. I want to pull both today's and yesterday's data into a new spreadsheet. The rows are separate buildings., ex Building 1, Building 2, Building 3.
Currently I can pull today's data no problem using VLOOKUP; how can I pull the next column when yesterday's date will always change? Please help!

Adding a screen shot of the report.

Building	09/11/2024	09/10/2024
7K-511	23.4 deg	24.2 deg
550-14	18.3 deg
20K-01	23.9 deg
350-07	17.8 deg
7K-515		24.9 deg
7K-513	24.5 deg	24.4 deg
7K-514	24.5 deg
7K-515	24.9 deg
7K-512	24.7 deg

[#2]

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

[#3]

@ladint 

I took the table you showed and produced the attached. It does use FILTER, as @m_tarler suggests in his response.

I suspect he'd also agree that you could organize your data differently for more effective longer term use. The way you have it, I presume you're plugging a new column in each day in column B, moving all preceding data over one. In time, you'll run out of columns. Excel could work just as well with a new row added each day for each building and temp reading, in any order. Let Excel do "the heavy lifting" of extracting the data for each building for any two days. If that's of interest, let us know.

[#4]

@ladint 

Just following up on the suggestion that a slight redesign might serve you well. 

This takes advantage of two semi-advanced features of Excel

• a table (which enables you to add new rows and have the new rows automatically included in any analyses)
• a data validation list so that you can just select the building code from the list; it also ensures it's entered correctly

And here's a piece of unsolicited advice: just enter the number under the temp column. We know it's a measurement in degrees, so those are just unnecessary additional keystrokes.

[#5]

@mathetes Thank you so much for the quick reply and assistance! Apologies if my original post wasn't clear or didn't provide enough information. The report I am pulling is from external software that I currently don't have ability to change layout, so I'm working with what I have shown. No additional keystrokes for "deg" either, that's how it's populating. 

The piece I can't figure out (and again apologies this wasn't clearer) is that I'd like to pull yesterday's temperature into a new Master tab, that pull referencing the Building name. It's why I am using VLOOKUP for other pieces of information; the VLOOKUP value is my building name. 

I have several different systems collecting data on each building; they all have the ability to export reports into Excel forms but don't talk to each other otherwise. I'm building a Master spreadsheet that compiles all data based on building, so I can export from systems each morning and have a clear picture. 

Thanks again for all your help.

[#6]

@ladint 

So the first example I gave you should give guidance as to how to achieve what you want. Just do that "FILTERing" on a different page. I appreciate that you think VLOOKUP is the answer, but be assured that is but one way--albeit a very popular one, having been around since the infancy of spreadsheets--to retrieve information according to criteria. Here's a resource of many others. I have found, however, that FILTER often works in precisely the situation you're in.

Having said all that, are you in a position to share an actual example of the raw data you're working with? Seeing that more comprehensive picture might help us help you. You may be able to attach file(s) directly below the text box into which you enter your inquiries and responses. Otherwise, posting them on OneDrive or GoogleDrive with links here that grant access is effective as well.