vlookup #N/A Problem: Lookup Value is a Formula Result

[Shooter]

Greetings,

Been working this problem for hours, one worksheet referencing another with vlookup.

Column A in both sheets contain times in 15 minute increments

ws1:
A3:A48 = time of day in 15 minute increments
B3:B48 = various activities in text

ws2:
A3 is a dropdown with a specific time (6:00 AM, 6:15 AM, ect.)
A4:A48 is next time in +15 minute increments A3+TIME(0,15,0)..A4+TIME(0,15,0).. and so on
B3:B48 contain vlookup formulas ex. B3 =vlookup($A3,'ws1!$A$3:$F$48,2,FALSE), B4 =vlookup($A4,'ws1!$A$3:$F$48,2,FALSE), ect...

Continues to return #N/A
I've checked =isnumber() and istext() for column A on both sheets, both are TRUE for ISNUMBER.
Problem appears to be with the formulas adding 15 minutes in Column A in ws2 (Lookup Values).

Can anyone assist? Greatly appreciated......

 

[Shooter]

Just a quick correction, the vlookup formulas do not have the ' (this was left over from a reference to another workbook).

The formulas I'm using are B3 =vlookup($A3,ws1!$A$3:$F$48,2,FALSE), B4 =vlookup($A4,ws1!$A$3:$F$48,2,FALSE), ect...

Any help is greatly appreciated.

 

[Gerald Higgins]

Hi, this COULD POSSIBLY be because time values in Excel are sometimes difficult to match to whole numbers or decimal values.

For example, 15 minutes is expressed as 0.01041666666...
which can be provided by the formula
=15/60/24

It MAY be that in WS1, these values have been approximated, to something like 0.01041667.
In which case a VLOOKUP won't always work.

If this IS the problem, I can think of at least two possible solutions.

1) Consider using the TRUE argument within the VLOOKUP - check Excel help if you're not sure of the reason for doing this
2) Consider changing the way time values are stored, either in WS1 or in WS2 - perhaps by enforcing the same rounding standards in both.
You can check if two values that you think should be the same, are actually the same, by a simple
=ws1!A3=ws2!A3
or similar.

 

[Shooter]

Thanks Gerald.

I've tried using the TRUE argument, and while I do not get errors, the return values are incorrect, usually off by 15 minutes.

I ran =ws1!A3=ws2!A3 down the entire sheet and got TRUE results with a few exceptions (which I cannot explain). All cells in both Column As are formatted the same using TIME in Format Cells, so no change.

I wonder if excel is rounding different when executing the calculation?

 

[Gerald Higgins]

Yeah, when you apply =ws1!A3=ws2!A3 and you get FALSE, are these values the ones that are problematic in the VLOOKUP as well ?

If yes, I think it suggests my idea about mismatches between values with recurring decimals may have been correct.

Like I said, consider changing the way the time values are stored, either in WS1 or in WS2, to ensure they are the same in both.

 

[Shooter]

I get #N/A for all of the vlookup results, even with the majority of TRUE =ws1!A3=ws2!A3 results.

Can you share what you mean about changing the way time values are stored?

 

[Gerald Higgins]

OK let's see if we can fix this.

Please can you give an example of two values, one from each sheet, which you think should match, but don't ?

If either or both of the values are calculated, please give full details of how they are calculated.