Help with the dreaded #NA in Vlookup

  • Thread starter Thread starter frayed
  • Start date Start date
F

frayed

Hello,
Even if it is an exact match I'm getting #NA.
I'm trying to add a 5 Minute period to a 15 Min period and date and
return the corresponding value. I know there is a problem with Binary
rounding and I've tried rounding, truncating, general formatting,
precision as displayed, and hair pulling. I'm lost here. The data is
formatted general formatting except in this example.
Just the Lookup's that I add the 5 Min to won't work.I would appreciate
any help. Thanks, Bill

Col H Row 2 =VLOOKUP(G2,C$2:D$5,2,0) Works OK
Col I Row 2 =VLOOKUP(Round(K$2+G2,6),C$2:D$5,2,0)
Doesn't Work and I've tried Trunc instead of Round
Col J Row 2
=VLOOKUP(Round(2*k$2+G2,6),C$2:D$5,2,0)
Doesn't Work and I've tried Trunc instead of Round

A B C D E
Date Time Date+Time Value Date
10/24/03 9:30 37918.39583 1 10/24/03
10/24/03 9:35 37918.39931 2 10/24/03
10/24/03 9:40 37918.40278 3 10/24/03
10/24/03 9:45 37918.40625 4 10/24/03

F G H I J
K
Time Date+Time Vlkup1 Vlkup2 Vlkup3 5 Min
9:30 37918.39583 1 #NA #NA .003472
9:45 37918.40625 4 5 6
10:00 37918.41667 7 8 9
10:15 37918.42708 10 #NA #NA

The actual spreadsheet is several thousand rows.
 
Hi Frayed,

This may not be what you're after, but why not just do the
lookup on the abs value and then round that. Something
like:

=ROUND(VLOOKUP(G2,C2:D5,2,0)+K2,6)

Biff
-----Original Message-----

Hello,
Even if it is an exact match I'm getting #NA.
I'm trying to add a 5 Minute period to a 15 Min period and date and
return the corresponding value. I know there is a problem with Binary
rounding and I've tried rounding, truncating, general formatting,
precision as displayed, and hair pulling. I'm lost here. The data is
formatted general formatting except in this example.
Just the Lookup's that I add the 5 Min to won't work.I would appreciate
any help. Thanks, Bill

Col H Row 2 =VLOOKUP(G2,C$2:D$5,2,0) Works OK
Col I Row 2 =VLOOKUP(Round(K$2+G2,6),C$2:D$5,2,0)
Doesn't Work and I've tried Trunc instead of Round
Col J Row 2
=VLOOKUP(Round(2*k$2+G2,6),C$2:D$5,2,0)
Doesn't Work and I've tried Trunc instead of Round

A B C D E
Date Time Date+Time Value Date
10/24/03 9:30 37918.39583 1 10/24/03
10/24/03 9:35 37918.39931 2 10/24/03
10/24/03 9:40 37918.40278 3 10/24/03
10/24/03 9:45 37918.40625 4 10/24/03

F G H I J
K
Time Date+Time Vlkup1 Vlkup2 Vlkup3 5 Min
9:30 37918.39583 1 #NA #NA .003472
9:45 37918.40625 4 5 6
10:00 37918.41667 7 8 9
10:15 37918.42708 10 #NA #NA

The actual spreadsheet is several thousand rows.


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
You shouldn't be rounding / truncating any date/time fields!

Enter 5 minutes in K as =TIME(0,5,0).

Then, just use VLOOKUP without any rounding/truncating stuff. I tested
using the sample data you posted and everything looked like it worked
just fine.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Sorry for late response-away from computer all day.

Tushar,
I tried what you suggested and it worked on the third lookup column bu
not the second one. The third column adds two 5 Minute periods and th
second column adds just one five minute period. I'm probably doin
something else wrong but I don't know what. I could send you a smal
sample file if that would help.

Biff,
This didn't work either.

Thanks to both of you.
Bil
 
If Tushar doesn't read your post you can email me a small sample and I have
a look at it.. Please include some formulas with errors as well.

you can email me at (e-mail address removed)

--

Regards,

Peo Sjoblom

frayed said:
Sorry for late response-away from computer all day.

Tushar,
I tried what you suggested and it worked on the third lookup column but
not the second one. The third column adds two 5 Minute periods and the
second column adds just one five minute period. I'm probably doing
something else wrong but I don't know what. I could send you a small
sample file if that would help.

Biff,
This didn't work either.

Thanks to both of you.
Bill


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
I have looked at the example and one solution was to round all date and time
values to the nearest minute,

=VLOOKUP(ROUND((K$6+G5)/TIME(,1,),0)*TIME(,1,),C$5:D$82,2,0)

when I did that for both the vlookup tables and the lookup values the NA
errors were gone

--

Regards,

Peo Sjoblom

frayed said:
Peo,

An email with file attached is on the way.

Thanks,
Bill


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
My Pleasure..

--

Regards,

Peo Sjoblom

frayed said:
Peo,

You're the best. It worked great.

Thanks,
Bill


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top