VLOOKUP and Non-numeric Data

[anthony.mcgovern]

I'm looking for help on a project I've been working on for months.

I’ve got a table in a workbook that reports daily Treasury yields from data found in the other tabs of the workbook. Those other tabs get the data from external sources. The cells in the table comprise of simple formulas that are instructed to find specific dates in the other tabs, retrieve the data corresponding to that date, and convert the data into percentages.

Here’s a picture of my table:

\1

The headers – those figures in blue – contain VLOOKUP functions that find approximate matches to the current date, the one-month prior date, and the one-year prior date. So the dates in the table headers are the most current date for which data on Treasuries yields are available (column 2 – Feb 1), the date from about one month prior (column 3 – Dec 31), and the date from about one year prior (column 4 – Jan 30, 2009), respectively. The cells below each header contain VLOOKUP functions that are linked to the dates in the headers of their respective columns.

OK. Here’s the problem. The format of the data that I get from external sources is not ideal. That is, depending on the day, the data in the other tabs that pull from external sources are sometimes not numbers. Instead of numbers, some of the data are dots – “.”

For example, here’s a picture of the data found in one of the tabs:

\1

As you can see, on both December 25th and January 1st, there are dots instead of numbers. (These are days when the market for Treasuries is closed.) My problem is that, for the cells in the third column, I don’t know of a sequence of formulas that will both only find dates when the market was open and that will retrieve only numerical data corresponding to that date.

Here’s a picture of my problem:

\1

In other words, the date header in the third column goes back to January 1, a day in which the market was closed. The cells below that header are linked to January 1 and are instructed to retrieve the data – numerical or not – corresponding to that date. (The cells are empty because I inserted a ISERROR into the formula that if true, returns a blank; the cells are retrieving a dot, which is then being divided by 100 – “./100” – clearly an error.)

What sequence of formulas will instruct Excel to ignore dates when the market was closed (dates with dots for data)? That is, what sequence of formulas will retrieve the date from about one month prior, if and only if, that date is one where the market was open (a date without a corresponding dot)? Conversely, what sequence of formulas will retrieve data, if and only if, they are numerical (and not a dot)?

The Excel file is attached to this post. If there are advanced Excel users on this forum who have dealt with this kind of problem before, your input is most welcome.

Thanks.

[DonkeyOte]

I'm not quite sure which formulae you're looking to update but I'd say:

There's no need for OFFSET in B2 on the Feb 1 & 2 sheets - you can just use LOOKUP which has the advantage of being shorter but more importantly is non-volatile

Please Login or Register  to view this content.

If the aim is such that C2 becomes the last date a month before B2 wherein data points exist you can again use LOOKUP but in a different guise:

Please Login or Register  to view this content.

D2 as above but using -12 rather than -1 in the EDATE

(I've used EDATE on assumption you're using XL2007)

If the dates are correct we assume all subsequent calcs are ok given they reference dates with valid data to be returned.


Of course I suspect it's the case that the B2 formula needs to be adjusted in a similar manner... ie last day <= last day listed for which valid data points exist.

[anthony.mcgovern]

Thanks, DonkeyOte. The LOOKUP function you provided certainly has made my table more stable.

The second formula you provided does do the job -- but I'm afraid I don't understand it yet. I'll have to really take a look at it piece by piece before I feel comfortable employing it.

On another note, I am using Excel 2007 but I'd like this table to be backwards compatible to Excel 2003. As useful as it is, I'll have to get rid EDATE function.

Thanks again.