So what I’m trying to do is have a formula lookup a value in a table with muliple columns and rows. In this particular example im trying to input daily historical yields from maruties year 1:30. (date horizontally and yields vertically)
Basically i want to be able to put in a maturity year and a historical date and have a it pull the approriate yield. (e.g., Maturity of 2016, on10/27 is 0.64. Seems like it should be something pretty easy but i cant think of how to do it with just a vlookup or something similar because the column number is variable.
VLOOKUP + Match is how to do it. I wouldn’t do the Vlookup and Hlookup. I have some fairly cool spreadsheets that do income and balance sheet change analysis. I used the VLookup/Match combo to enable me to drop down and choose different dates and it slots automatically.
Once you have the VLookup, I’d use Data Validation to only allow a selection of a column variable that exists. That will eliminate user errors regarding inputting incorrect or improperly formatted dates.
+1 Vlookup + Match Vlookup(Year, entire table, match(date, first row, 0), false) The match will return a number, in this case the number of columns you need to move just like you would input manually in regular Vlookup. Have cells where you select the variables (year and date) and reference those cells in your formula instead of having to change it in the formula. As mentioned, it would be a good idea to use data validation to limit which values can go into these cells to avoid user error.
It may be appropiate to use index, I still haven’t learned it well enough yet. Any reason to think its better then vlookup/match? I think I remember vlookup is slower with large amounts of data than index.
I almost feel like using Index/Match/Match would be slower since two matches have to happen and you have to use an array formula. I’ve had this be slow in the past but it could have just been the nature of my data.
i did both v + h lookup and v lookup + match. Both work great, but im going with v + match. Makes a little more sense and you dont have to add an extra row little better looking. Index looks pretty heady may have to see whats good
edit: tried out index works pretty good and was more along the lines of what i thought the formula would be like when i started so im using that. and i plan on this being a fairly large data set, so hopefully index will be best long term. thanks for all the help
I often just use OFFSET for things like this. It’s very flexible and often doesn’t require selecting any region as the “array” as the lookup and index functions do.
The thing that gives the index greater power is that let’s assume your data comes in differently everytime you pull it. Example:
1st Pull: Date Account Name Market Value Return
2nd Pull: Account Name Return Date Market Value
3rd Pull: Market Value Date Return Account Name
The index is not looking up the column or first row, it’s looking up the column or row your information is in you want. So your ability to pull information from different sources and it’s more dynamic and less likely to break down. That’s how I see it.
This is the correct answer. Index works no matter how your data is organized. With vlookup, sometimes you have to manually manipulate the data before the function will work.
