Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help to in setting up a proper Vlookup table and formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help to in setting up a proper Vlookup table and formula
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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help to in setting up a proper Vlookup table and formula
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)),"",VLO OKUP(ROUND(D8,2),Frate,3,TRUE)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help to in setting up a proper Vlookup table and formula
On Dec 26, 12:43*am, "Max" wrote:
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 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)),"",VLO OKUP(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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help to in setting up a proper Vlookup table and formula
Welcome, George. Glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need help desparately with PROPER formula... | Excel Worksheet Functions | |||
VLOOKUP in a dynamic setting | Excel Worksheet Functions | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
How to use Proper formula? | Excel Worksheet Functions |