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

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,A1:D5,MATCH("Small",A1:D1,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,A1:D5,MATCH(MID(I6,FIND("-",I6,3)+1,255),A1:D1,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"))))))