help to in setting up a proper Vlookup table and formula

[Realitygdk]

I need your assistance in properly using and formatting a Vlookup
Formula or a Vlookup table. I have attempted to use Lookup, Vlookup,
and HLookup tables, just cannot get the result that I need.

On my Rate Page, A1:K52, sheet 1, I have three listings of postage
rates in increments of less than an ounce to five pounds; ounces along
with corresponding rates are in fractional increments of 16th, for
example, 0.41 cents equals 1/16; 0.58 equals 2/16; .75 equals 3/16
and so on, alongside their weight labels, such as, 0.41 cents equals
1/16 ounce; 0.58 equals 2/16 ounce; .75 equals 3/16 ounce and so on,
as each element has its own column the table appears as follows:

A1:C52, Name Range is FRate, my VLookup Fraction Table Example;
0.41 1/16 ounce
0.58 2/16 ounce
0.75 3/16 ounce

E1:G52, Name Range is DRate, my VLookup DRate Table Example;
0.41 1 ounce
0.58 2 ounce
0.75 3 ounce

I1:K52, Name Range is D2RRate, my VLookup D2RateTable Example;
0.41 0.0625 ounce
0.58 0.125 ounce
0.75 0.0625 ounce

Then on my Department Sheet, Sheet 2 I have row for each day of the
month for a year and columns from

The following are two date samples, A8:V9:

Date Dept Pieces Rate Amt
Row 8 =1-Jan-08 Administration 1 $0.41 $0.41
Row 9 =2-Jan-08 Administration 2 $0.41 $0.82

This first section work great.
Column
A= Date
B=Department
C=Number of Pieces
D=C8*E8 , which equals .41

First VLookup Table is FRate

Fozib Flabel Fozib Flabel
Row 8 = 1/16 ounce 1/16 ounce
Row 9 = 1/16 ounce 2/16 Ounce*
G=Vlookup(D8,Frate,2,False)
H=Vlookup(D8,FRate,3,False)
I=C8*G8
J=Is suppose to be the total weight and is to read Vlookup(D8,Frate,
3,False), but the only way I can make it works is to have it read *as
follows: If(I8<= 0.8125, "ounce", "pound")

Second VLookup Table is DRate

Pieces Dozib Dlabel Dozib Dlabel
Row 8 =1 1 #N/A 1 #N/A
Row 9 =2 1 #N/A 2 #N/A
L=C8 which is correct for it is number of pieces 1
M=Vlookup(D8,Drate,2,False) is correct .41 equals 1 ounce.
N= Vlookup(D8,Drate,3,False) which is returning a #NA, should return
'Ounce'.
O=L8*M8
P=Vlookup(O8,Drate,3,False), which is also returning a #NA, but
should read Ounce.

Third VLookup Table is named D2Rate

Pieces D2ozib D2label D2ozib D2label
Row 8 =1 0.0625 ounce 0.0625 #N/A
Row 9 =2 0.0625 ounce 0.125 #N/A
R=C8 which is correct for it is the number of pieces entered at this
rate which is 1
S=Vlookup(D8,Drate,2,False) is correct .41 equals 0.0625 ounce!
T= Vlookup(D8,Drate,3,False) ounce is correct for .41!
U=R8*R8
V=Vlookup(O8,Drate,3,False), which is also returning a #NA, but
should read Ounce.

Why the returning ounces and weight is correct in this Vlookup, do
fraction needed to be converted
How to avoid the #NA?
I have attempted =if(isseror(Vlookup(D8,Drate,3,False) )) and I am
told that I have an error or missing parenthesis or an error in the
formula

Am I not capturing the correct number to be lookup. I have tried abs
(o8) and abs(u8) no success, wrong formula usages.

I blundered in my last attempt whereby I inserted a file in my request
for help. Sorry for having taken up such valuable user time by the
viewers of this user site. The problem seem to overwhelming, or else
I was pushed for time, whereupon, I just could not seem to put the
problem in proper wording, which I may have failed to do so again.
I only hoping that someone will take the time to review and assist me
in correcting this second attempt to locate my error in my use of a
VLookup formula.
Thank you for sharing your time with me in this personal matter,

George

 

[Max]

Some thoughts ..

J=Is suppose to be the total weight
and is to read Vlookup(D8,Frate,3,False)

In J8: =VLOOKUP(D8,Frate,3,FALSE)
seems to return correctly when tested here.

But since the vlookup's looking for an exact match (FALSE), there could be
problems if the underlying value in D8 (calculated?) doesn't really match
the lookup values in Frate's 1st col (eg: a calculated value of say, 0.4099
won't be equal to 0.41, although it looks like 0.41 if the cell is formatted
to 2 dp)

Think you could try using TRUE instead in J8:
=VLOOKUP(D8,Frate,3,TRUE)
since the lookup col's values in Frate are in ascending order

Or, alternatively, try something like this in J8:
=VLOOKUP(ROUND(D8,2),Frate,3,FALSE)
which rounds the calculated value in D8 to 2 dp

As for:

P=Vlookup(O8,Drate,3,False),
which is also returning a #NA, but should read Ounce. &
V=Vlookup(O8,Drate,3,False),
which is also returning a #NA, but should read Ounce.

You're getting #N/A errors for the above simply because the vlookup's table
array: Drate is no longer valid for the lookup value in O8. The actual
lookup col (for O8) is col F in Rate, but Drate's 1st col (the vlookup col)
is col E. So naturally, the #N/As.

One quick fix is to define a new range,
eg: Drate1 =Rate!$F$1:$G$52 (ie with the 1st col = col F),
then you could use in both P8 and V8:
=VLOOKUP(O8,Drate1,2,FALSE)

---

 

[Max]

As to your lines:

How to avoid the #NA?

I have attempted =if(isseror(Vlookup(D8,Drate,3,False) ))

The first step is to ensure that the vlookup is indeed working properly.
The earlier suggestions should take care of that.

For error trapping of "true" #N/A returns,
you could use IF(ISNA(...), in this manner:
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
which will return "blanks", viz.: "" for any unmatched cases
(you could amend the "blanks" return to suit)

An example would be, say in J8:
=IF(ISNA(VLOOKUP(ROUND(D8,2),Frate,3,TRUE)),"",VLOOKUP(ROUND(D8,2),Frate,3,TRUE))

---

 

[Realitygdk]

As to your lines:


The first step is to ensure that thevlookupis indeed working properly.
The earlier suggestions should take care of that.

For error trapping of "true" #N/A returns,
you could use IF(ISNA(...), in this manner:
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
which will return "blanks", viz.: "" for any unmatched cases
(you could amend the "blanks" return to suit)

An example would be, say in J8:
=IF(ISNA(VLOOKUP(ROUND(D8,2),Frate,3,TRUE)),"",VLOOKUP(ROUND(D8,2),Frate,3,­TRUE))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---  

Outstanding, Max OUTStanding, OutSTANDING!!!

Thank you Max: Thank you for plowing through my muddled word problem;
gleaning a solution from the mis-information and understated
information; for creating the logic needed in the solution that would
fix my error and most of all for taking the time to derive an idiot
proof solution for me, which is the only solution I would have been
able to grasp.

What a wonderful gift, as you can now understand, I truly wanted to
say many thanks to you Max.

Please have a safe Holiday and thanks for putting my mind at ease, so
that I can now Welcome the New Year in with Excel treats,

George

 

[Max]

Welcome, George. Glad it helped.

---