VLOOKUP and Non-numeric Data

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.