Excel Lookup (mult columns and rows)

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)

10/30 10/29 10/28 10/27 10/26 ……………………………………………………….

2014 .50 .48 .52 .62 .38

2015 .55 .53 .57 .59 .48

2016 .60 .58 .62 .64 .66

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.

Any thoughts?

If you inserted a row with column numbers right under the dates you could do an hlookup on the date to get the column number.

In other words something like vlookup([year], [array of data], hlookup([date], [array of data], 2, false),false)

Vlookup with a match dog.

=vlookup(year, whole table,match(month and day,a1:a6,exact),false)

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.


This is really the right place for the index function to be used. Type in what you want in the reference cells (ex 2015, DATE)

+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.

Index is ‘more efficient’ for bigass tables. Vlook/match gsd’s.

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.

This is the way that I do it.

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.