If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup-Doesn't return values under 1
I have Sheet 2 with 6 columns, one of them with ascending numbers on column A
0-8.99 with two decimals. The other 5 columns are payouts per title and if pertaining to a small, medium or large category (A-Yes-Small, A-Yes-Medium, A-Yes-Large, B-Yes-Small, B-Yes-Medium, C-Yes-Large). I am asking the formula on sheet 1 to go into Sheet 2 to look up the numbers and find the exact match. It seems to work fine but only for those numbers above "1.0"....anything below 1.0 gives me a N/A error. I made sure both sets of numbers are formated the same and have the same column width using "Text to Columm"...what else can I do? On the other hand is there a simpler way to get this done as opposed to listing the numbers 0-8.99? I need to get the values if a number falls between any of the ranges below. SO for example if the person got 5 cars and he is in a Small category he gets 20...a different rate goes for positions A & B. If is under 0.1 then the result should say "None". I have a grid as follows: Small Medium Large If 0.1-2.99 10 15 15 If 3.0-4.99 15 20 20 If 5-6.99 20 25 30 If 7-8.99 25 35 40 =IF($I$6="A-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$B$901,2,FALSE),IF( $I$6="A-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$C$901,3,FALSE),IF ($I$6="A-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$D$901,4,FALSE),IF( $I$6="B-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$E$901,5,FALSE),IF( $I$6="B-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$F$901,6,FALSE),IF ($I$6="B-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$G$901,7,FALSE),"No ne")))))) |
#2
|
|||
|
|||
Vlookup-Doesn't return values under 1
testing in 2003, I don't have any problems getting vlookup to find fractional
values less than 1. the n/a# means that you aren't getting an exact match. Try (in an unused cell =A1=B1 where A1 is the a sample fractional value on sheet 1, and B1 is the value you expect it to match on sheet 2 as part of the vlookup. My money is that they don't actually match. Also, to shorten your formula, consider using a named range; You could create one called "MyVLup" referencing Sheet2!$A$2:$G$901 and shorten use it across the formula: =IF($I$6="A-Yes-Small",VLOOKUP(F15,MyVLup,2,FALSE),IF($I$6="A-Yes-Medium",VLOOKUP(F15,MyVLup,3,FALSE),IF($I$6="A-Yes-Large",VLOOKUP(F15,MyVLup,4,FALSE),IF($I$6="B-Yes-Small",VLOOKUP(F15,MyVLup,5,FALSE),IF($I$6="B-Yes-Medium",VLOOKUP(F15,MyVLup,6,FALSE),IF($I$6="B-Yes-Large",VLOOKUP(F15,MyVLup,7,FALSE),"None")))))) Why bother? well, mostly because if your source range ever changes, you don't have to go in and update every vlookup reference in every cell where you have this, you just update it once in the named range. (I suspect it might also calculate faster, but I haven't any proof) As for an alternative; the problem is that (at least in 2003, I can't speak to 2007) there is a limit of 7(?) embedded levels of formula. If you were willing to break it into three columns (and maybe sum them for your total column) you could do something like: =if(find("Small",I6)0,if(F153,10,if(F155,15,if( F157,20,if(F15=7,25,""))))) so that would only have a number if the source had the word small; the next column over would be for medium, then large. HTH, Keith "Carolina" wrote: I have Sheet 2 with 6 columns, one of them with ascending numbers on column A 0-8.99 with two decimals. The other 5 columns are payouts per title and if pertaining to a small, medium or large category (A-Yes-Small, A-Yes-Medium, A-Yes-Large, B-Yes-Small, B-Yes-Medium, C-Yes-Large). I am asking the formula on sheet 1 to go into Sheet 2 to look up the numbers and find the exact match. It seems to work fine but only for those numbers above "1.0"....anything below 1.0 gives me a N/A error. I made sure both sets of numbers are formated the same and have the same column width using "Text to Columm"...what else can I do? On the other hand is there a simpler way to get this done as opposed to listing the numbers 0-8.99? I need to get the values if a number falls between any of the ranges below. SO for example if the person got 5 cars and he is in a Small category he gets 20...a different rate goes for positions A & B. If is under 0.1 then the result should say "None". I have a grid as follows: Small Medium Large If 0.1-2.99 10 15 15 If 3.0-4.99 15 20 20 If 5-6.99 20 25 30 If 7-8.99 25 35 40 =IF($I$6="A-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$B$901,2,FALSE),IF( $I$6="A-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$C$901,3,FALSE),IF ($I$6="A-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$D$901,4,FALSE),IF( $I$6="B-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$E$901,5,FALSE),IF( $I$6="B-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$F$901,6,FALSE),IF ($I$6="B-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$G$901,7,FALSE),"No ne")))))) |
#3
|
|||
|
|||
Vlookup-Doesn't return values under 1
If I look up value 0.33 (A1) in sheet 2 (B1) I get a FALSE error...so you
were right....what can I do to get them to match if both say 0.33? Any chance I can share the actual document with you? Thanks for your time ! Carolina "ker_01" wrote: testing in 2003, I don't have any problems getting vlookup to find fractional values less than 1. the n/a# means that you aren't getting an exact match. Try (in an unused cell =A1=B1 where A1 is the a sample fractional value on sheet 1, and B1 is the value you expect it to match on sheet 2 as part of the vlookup. My money is that they don't actually match. Also, to shorten your formula, consider using a named range; You could create one called "MyVLup" referencing Sheet2!$A$2:$G$901 and shorten use it across the formula: =IF($I$6="A-Yes-Small",VLOOKUP(F15,MyVLup,2,FALSE),IF($I$6="A-Yes-Medium",VLOOKUP(F15,MyVLup,3,FALSE),IF($I$6="A-Yes-Large",VLOOKUP(F15,MyVLup,4,FALSE),IF($I$6="B-Yes-Small",VLOOKUP(F15,MyVLup,5,FALSE),IF($I$6="B-Yes-Medium",VLOOKUP(F15,MyVLup,6,FALSE),IF($I$6="B-Yes-Large",VLOOKUP(F15,MyVLup,7,FALSE),"None")))))) Why bother? well, mostly because if your source range ever changes, you don't have to go in and update every vlookup reference in every cell where you have this, you just update it once in the named range. (I suspect it might also calculate faster, but I haven't any proof) As for an alternative; the problem is that (at least in 2003, I can't speak to 2007) there is a limit of 7(?) embedded levels of formula. If you were willing to break it into three columns (and maybe sum them for your total column) you could do something like: =if(find("Small",I6)0,if(F153,10,if(F155,15,if( F157,20,if(F15=7,25,""))))) so that would only have a number if the source had the word small; the next column over would be for medium, then large. HTH, Keith "Carolina" wrote: I have Sheet 2 with 6 columns, one of them with ascending numbers on column A 0-8.99 with two decimals. The other 5 columns are payouts per title and if pertaining to a small, medium or large category (A-Yes-Small, A-Yes-Medium, A-Yes-Large, B-Yes-Small, B-Yes-Medium, C-Yes-Large). I am asking the formula on sheet 1 to go into Sheet 2 to look up the numbers and find the exact match. It seems to work fine but only for those numbers above "1.0"....anything below 1.0 gives me a N/A error. I made sure both sets of numbers are formated the same and have the same column width using "Text to Columm"...what else can I do? On the other hand is there a simpler way to get this done as opposed to listing the numbers 0-8.99? I need to get the values if a number falls between any of the ranges below. SO for example if the person got 5 cars and he is in a Small category he gets 20...a different rate goes for positions A & B. If is under 0.1 then the result should say "None". I have a grid as follows: Small Medium Large If 0.1-2.99 10 15 15 If 3.0-4.99 15 20 20 If 5-6.99 20 25 30 If 7-8.99 25 35 40 =IF($I$6="A-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$B$901,2,FALSE),IF( $I$6="A-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$C$901,3,FALSE),IF ($I$6="A-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$D$901,4,FALSE),IF( $I$6="B-Yes-Small",VLOOKUP(F15,Sheet2!$A$2:$E$901,5,FALSE),IF( $I$6="B-Yes-Medium",VLOOKUP(F15,Sheet2!$A$2:$F$901,6,FALSE),IF ($I$6="B-Yes-Large",VLOOKUP(F15,Sheet2!$A$2:$G$901,7,FALSE),"No ne")))))) |
Thread Tools | |
Display Modes | |
|
|