Vlookup Inconsistancys...

[Pete Clarke]

Hi All,

In need of some help here as i'm stumped!

I've created a spreadsheet that has various columns which show the temperatures at 5 minute intervals throughout the office. Each column is for a different zone so I can see at any one time, which parts of the office are hot or cold.

i.e. A B C D

1 Time Zone 1 Zone 2 Zone 3
2 15:00 22.3 22.4 23
3 15:05 22.3 22.5 23.8
4 15:10 22.5 23.1 24

To make things a little more visual, next to these columns i've created a rough outline of the office and formulated each cell with a vlookup formula which references any particular inputted time with the columns containing the temperature data. The cells within the office outline are conditioned so change colour according to the temperature returned.

For example; in the 'Time Of Day' cell I might input '15:00'. The formula cross references this time with column 'A' and returns the temperature according to whether I have requested to take data from column B, C, D and so forth.

Formula:
=VLOOKUP($R$2,$A$11:$A$298:$B$11:$I$298,2,FALSE)

$R$2 is the 'time of day' cell.
$A$1:$A$208 represents column A
$B$11:$I$298 represents cells B2 - D4

My problem is that sometimes when inputting a time of day to reference against, I get the correct returned value and is works perfectly, when inputting other times of the day I get an error value of '#N/A', even though all the data is present and correct. Other times I will get a value of '22' for each cell within a zone, even though the correct temperature is 22.8 for instance. All formular are exactly the same and I cannot work out why different values or no values are being returned?

Any help would be much appreciated!

 

[VoG]

That is a rather oddly constructed formula. Does this fare any better?

=VLOOKUP($R$2,$A$11:$I$298,2,FALSE)

 

[Pete Clarke]

Hi,

see what you've done, but in short, no.

It's very strange. having put that formular into a row of cells, I get the following results.

12:00 - All cells report well
12:05 - All cells bar the row with the new formula work
00:00 - none work
00:05 - none work bar the row with the new formula
05:00 - All cells bar the row with the new formula work
05:30 - All cells bar the row with the new formula and 'zone 5' work
06:00 - All cells bar 'zone 8' work

There seems to be no rhyme or reason to it...

 

[shg]

What are the time values in col A?

The False as the last argument in VLOOKUP requires an exact match -- not just to the precision shown in the cells, but the exact value.

 

[Pete Clarke]

The values in column A start at 13:30 and go through 24 hours at 5 minute intervals, so the last value being 13:25 the next day.

Thanks.