PDA

View Full Version : Solved: Vlookup and negative numbers



pedrovarela
09-25-2008, 02:59 AM
Hi,

In order to show numbers in percentages (from 0,00 to 1,00) in colours I created the following Name (clsValues) where cls are asigned to colours.

0 cls1
0,2 cls2
0,35 cls3
0,5 cls4
0,6 cls5

I used =VLOOKUP(data;clsValues;2;TRUE) and it worked well.

Now I'm trying to assign colours to percentages including negative percentages (from -1,00 to 1,00).

I created a Name called "clsValues" and used "
=VLOOKUP(Data;clsValues;2;TRUE)
"

-0,1 cls1
-0,03 cls2
0,03 cls3
0,03 cls4
0,1 cls5

But I get an error. How can I handle vlookup, and negative %?

Thanks!!!!!!

Bob Phillips
09-25-2008, 03:15 AM
It is working fine for me, as I understand what you are saying. Can you post a workbook?

pedrovarela
09-25-2008, 03:32 AM
Hi,

Ok. Find attached the workbook. Table2Value is the one that doesn't work.

Thanks!!

Bob Phillips
09-25-2008, 03:42 AM
The lookup value 04DIG doesn't exist in Table2Values, so it is no wonder it fails.

Bob Phillips
09-25-2008, 03:43 AM
=VLOOKUP(gainvalue,table2Values,2,TRUE)

works fine.

Bob Phillips
09-25-2008, 03:45 AM
or even

=VLOOKUP(VLOOKUP(region,Data,3),table2Values,2,TRUE)

pedrovarela
09-25-2008, 04:34 AM
Thanks!!!! You were right!