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"))))))

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(F15<3,10,if(F15<5,15,if( F15<7,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"))))))

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(F15<3,10,if(F15<5,15,if( F15<7,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"))))))