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

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




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


To derive "Small" "High" from the text "A-Yes-Small" try the formula

So when you combine this

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.

Ask a Question

Similar Threads

If/Vlookup override 4
Need VLOOKUP to Work Two Ways 0
VLOOKUP formula 3
Nesting countif functions 1
yes/ No on multiple sheet 2
vlookup 3 sheets 1