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
With table arranged as below try the below formula
Col A Col B Col C Col D Range Small Medium Large 0 10 15 15 3 15 20 20 5 20 25 30 7 25 35 40 Lookup value in cell A7 =IF(A7,VLOOKUP(A7,A15,MATCH("Small",A11,0),1), "") To derive "Small" "High" from the text "A-Yes-Small" try the formula =MID(I6,FIND("-",I6,3)+1,255) So when you combine this =IF(F15,VLOOKUP(F15,A15,MATCH(MID(I6,FIND("-",I6,3)+1,255),A11,0),1),"") -- Jacob "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 | |
|
|