# Vlookup-Doesn't return values under 1

C

#### Carolina

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

Ad

J

#### Jacob Skaria

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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.