Help with the dreaded #NA in Vlookup

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.
 
B

Biff

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
 
T

Tushar Mehta

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
 
F

frayed

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
 
P

Peo Sjoblom

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
 
P

Peo Sjoblom

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
 
P

Peo Sjoblom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count entries in the column translating to minutes. 5
Calculation error in Workhours 3
REQUEST FORMULA FOR COUNTING & SUM IN DUPLICATES 8
Finding the minimum in a selected number of rows of the same colum 5
How can find a value using two different matching criteria? 6
vLookup with dates 17
help with datedif function please 4
Problem w/vlookup 5

Top