Lookup Maximum Date and Corresponding Adjacent Value in Array with Column Specified by Re

[bkanne]

Using the Federal Reserve Bank of St. Louis' Add-In, I am able to download historical rate data for multiple different securities (10-Year Treasury, 3-Month LIBOR, etc.) This is incredibly useful because I can update this data with by simply running the macro and it gives me all the historical data alongside the newest information.

The information looks like this after I run the Macro:

Table.JPG

The data extends all the way down until the date with the latest information.

On a different tab, I need to be able to lookup what the maximum date in each range is and corresponding value next to it by identifying date column/corresponding value column via the codes present in A1 (DGS3MO) and A3 (DGS1). Of course, it has to adapt to when I update the macro and new dates and values are added. In addition, I have a bunch more rates than what I have shown in the example, so it would be great if the formulas wouldn't be restricted to just these four columns, but also included the ability to adapt to adding new columns as well.

I've tried a variety of different vlookup, hlookup, and index/match combinations but just haven't been able to get it to work. Any help would be GREATLY appreciated. Thank you so much!

[FDibbins]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[bkanne]

Sure, no problem!

Rate Data.xlsx

I had to delete a bunch of the line items out to meet the size requirements but this should be enough.

On the second tab, I created a non-dynamic if statement that simulates what I'm trying to do, but it doesn't help me if I add new items.

Thanks!

[FDibbins]

This was an intersting 1, had to use a bunch of different techniques

See the attached, I put my answers in a table below yours, for comparison. It is all automated now, I tested by adding a new column if data (which you can obviously delete). Not sure what the ID number was for (column C)? If you dont need it, you can delete it

let me know what you think?

[bkanne]

This is brilliant! Thank you so much.

The only thing I had to change were the heights in the Offset formulas to compensate for much longer data sets. Otherwise this is incredible and saves me from writing 1,000 character If statements. Thank you again!

[FDibbins]

The data being pulled in column D may be a bit long, you can truncate it by playing around with this...
=LEFT(SUBSTITUTE(OFFSET('Rate Data'!$A$1,4,ROW(A1)*2-2)," ","xx",2),FIND("xx",SUBSTITUTE(OFFSET('Rate Data'!$A$1,4,ROW(A1)*2-2)," ","xx",2),1)-1)

THanks for the kind words and feedback too

[FDibbins]

I had another look at this. Would it be possible to put your data "sets" 1 below the other, instead of side by side? If so, you could baybe simplify things a bit. See the new sheet I added, and the new table I created below the other 2

[bkanne]

I see what you did here and I like it- the only issue is that my datasets are constrained by the add-in that I use to download them (Federal Reserve Bank of St. Louis Excel Add-In). This requires that the information is downloaded side-by-side instead of stacked on top as you have done in your new sheet.

http://research.stlouisfed.org/fred-addin/


A workaround for this would be to write a macro that would do this with the new data after the add-in downloads the data, but then it wouldn't be updateable; the add-in requires that the name of the dataset is in the first row.

The first formulas you wrote are working great for me though, I haven't had any issues with them yet. After making the adjustments to the height in the offset function, it is able to capture the latest date by dataset name in over 40 different rate categories.

I would upload the file here but it is too large.

[FDibbins]

OK, if thats how you get your data (side-by-side), there probably isnt much to be gained by my 2nd approach. I thought you got a "set" of 2 columns at a time, and copied them into place next to each other yourself.

Thanks for your reply, look forwatd to helping you again soon